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

Question

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 need a GROUP BY?

What dialect of SQL are you working with? – Jamie F Oct 10 '11 at 23:52 — Jamie F, Oct 10 '11 at 23:52

Jamie F · Accepted Answer · 2011-10-11T01:29:39.697

3

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.

edited Oct 11 '11 at 01:29

answered Oct 10 '11 at 23:50

Jamie F

23,189
5
61
77

What if names are repeated in table1? – Y_Y Oct 11 '11 at 00:21
1

It's fine if names are repeated, as long as they would always have a different room attached. That is, the combination of firstname, lastname and roomname is unique for every row. – Jamie F Oct 11 '11 at 00:22
what if they do have the same room attached? this is basically the problem I am facing right now... – Y_Y Oct 11 '11 at 01:20
I've changed my answer slightly. It should now handle this. *But* once you are getting entirely duplicate rows, you are in a bad place. Either your tables are structured poorly, or your approach to the problem is wrong (i.e. you are asking the wrong question). – Jamie F Oct 11 '11 at 01:32

score 1 · Answer 2 · edited May 23 '17 at 10:24

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

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

2 Answers2