How to select data from database given an include ALL restriction?

I am currently learning how to program in SQL. I have a table1 with firstname, lastname and roomname.

And I have another table2 that has all the roomnames.

I was wondering how to select firstname and lastname from those people who have been in ALL of the rooms. Do I n开发者_如何学Goeed a GROUP BY?

For TransactSQL, if you know that every row in your first table is distinct, then this would work:

SELECT
   firstname,
   lastname
FROM
   table1
GROUP BY
   firstname,
   lastname
HAVING
   COUNT(DISTINCT RoomName) = (SELECT COUNT(*) FROM table2)

This should be pretty efficient, but not too flexible, in case you wanted to ignore certain rooms.

Group by is a command in order to group your results

As I can see you have duplicates in table1: Let's say:

**table1:**
firstname lastname roomname
Theodore  Hello    roomA
Mike      World    roomA
Theodore  Hello    roomB
Theodore  Hello    roomC
NickThe   Greek    roomC

And **table2:**
roomname
roomA
roomB
roomC

As you can see Theodore is the one that had passed from all rooms from table2. In common language (a good approach for constructing SQL queries) you would say: Grab the person(s) from table1 who have passed from all the rows of table2:

Take a look at here: Fetching only rows that match all entries in a joined table (SQL)

But as Jamie said this is not a proper sql syntax and way of thinking