36

Say I have a table:

Restaurant locations: 

RESTAURANT_NO | RESTAURANT_LOCATION
-----------------------------------
1             |            City A
1             |            City B
2             |            City A
2             |            City B
2             |            City C
3             |            City C
4             |            City A
4             |            City B

How would I be able to group them together and also only select the RESTAURANT_NO that do not have locations in city C?

Using this example, I want to return:

 RESTAURANT_NO
 -------------
 1
 4

Since RESTAURANT_NO 2 and 3 both have locations in city C.

I do not know how to group RESTAURANT_NO together while also trying only to select the groups that meet this requirement.

EDIT: I got this working.

However, there is one last thing that I still have not been able to figure out. The following table has the ID number of people along with cities they have worked in:

PERSON_NO | CITY_NAME
---------------------
1         |    City A
2         |    City B
3         |    City A
3         |    City B
3         |    City C
4         |    City A
4         |    City B
4         |    City C

How would I be able to get the PERSON_NO of all the people who have lived in all three cities, A,B, and C?

I want to return

PERSON_NO
---------
3
4

Thanks, again. I haven't had that much experience with SQL and so I'm not sure what to do.

vesselll
  • 423
  • 1
  • 5
  • 10

3 Answers3

40

One way:

SELECT RESTAURANT_NO FROM restaurant WHERE RESTAURANT_NO NOT IN
(SELECT RESTAURANT_NO FROM restaurant WHERE RESTAURANT_LOCATION = 'City C')
John Pick
  • 5,562
  • 31
  • 31
  • I've tested my DB with this query and it seems to work. Thanks. – vesselll Feb 20 '12 at 02:12
  • I've tested in my Database with above query,it is working fine,Thank you – Rajesh Om Nov 15 '18 at 12:35
  • 1
    In case you're using this (good!) answer for your own purposes, remember that you will get no results at all if the subquery returns NULL, i.e. RESTAURANT_NO in this case). It's never NULL in this particular example, but for the more generic use you might want to insert an IS NOT NULL check. :-) – Jonas May 15 '20 at 10:03
  • @Jonas how would you incorporate IS NOT NULL check in the above statement? – Al Guy Sep 20 '20 at 14:03
  • 1
    @AlGuy I know you didn't ask me, but here's a way: `SELECT RESTAURANT_NO FROM restaurant WHERE (RESTAURANT_NO NOT IN (SELECT RESTAURANT_NO FROM restaurant WHERE RESTAURANT_LOCATION = 'City C') OR (RESTAURANT_NO IS NULL AND NOT EXISTS (SELECT 0 FROM restaurant WHERE RESTAURANT_NO IS NULL AND RESTAURANT_LOCATION = 'City C'))` – John Pick Sep 21 '20 at 16:26
10
SELECT DISTINCT
      Restaurant_no
FROM 
      TableX t
WHERE 
      NOT EXISTS
      ( SELECT *
        FROM TableX c
        WHERE c.Restaurant_no = t.Restaurant_no
          AND c.Restaurant_location = 'City C'
      )
ypercubeᵀᴹ
  • 113,259
  • 19
  • 174
  • 235
6

Use DISTINCT.

try this:

SELECT DISTINCT t.Restaurant_No
FROM Restaurant t
WHERE t.Restaurant_No NOT IN
       (SELECT s.Restaurant_No  
        FROM  Restaurant s
        WHERE s.RESTAURANT_LOCATION = 'City C')
ORDER BY t.Restaurant_No
John Woo
  • 258,903
  • 69
  • 498
  • 492