fetch id who have register all adv

Question

There are 3 types of adventures for which I used distinct function in query.
There is only one 1 customer who have booked all types of adventures.
The query i used to fetch the data is:

select c.customerid,c.name
    from customer c 
        inner join booking b
            on c.customerid = b.customerid 
        inner join destination d
            on b.destinationid=d.destinationid
        inner join adventure a
            on d.adventureid=a.adventureid
    group by c.customerid
    having count(distinct b.bid)=(select count(*) from bid)
        or count(distinct a.adventuretype)=(
            select count(distinct a.adventuretype)
            from adventure
        )

Gordon Linoff · Answer

You can get the customer ids using aggregation and having:
select b.customerid
from booking b join
     destination d
     on b.destinationid = d.destinationid join
     adventure a
     on d.adventureid = a.adventureid
group by b.customerid
having count(distinct a.advtype) = 3;

Or, if you don't want to hardcode the "3", you can use:
having count(distinct a.advtype) = (select count(distinct advtype from adventure)

I'll leave it up to you to add in the customer name (using join, exists, or in).

fetch id who have register all adv

One Answer

Add your own answers!

Ask a Question