fetch id who have register all adv

Admin 1
Viewed 518

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
        )
mysql sql
1 Answers

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).

user_11440350
Related