mysql - How can I find common values in one table column based on another column using AND?

Question

I have a cross reference table and need to find common values of one ID where the second ID has multiple values given in the query. This is easy to do with an "OR" but I can't for the life of me figure out how to do it with an "AND". I'm sure there is an obvious solution that has been staring at me for these many hours but I can't find it despite much testing and searching. Any help is much appreciated.

cross_reference_table with columns: id, location, attendee

Using OR is easy:

SELECT DISTINCT location FROM cross_reference_table
WHERE attendee = 6 OR attendee = 7 OR attendee = 8;

But I need to return only those locations where attendees 6,7 and 8 were all in attendance. Again, I can't simply use an AND in the query structure above. I've been beating my head against this and simply can't make any progress. Thanks in advance for any suggestions.

Answer 1

Try something like this

select distinct location
From cross_reference_table
where attendee =6 or attendee = 7 or attendee=8
group by attendee, location
having count(*)=3