CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2008-r2
aantiix
aantiix

Reputation: 534

Find where not exists, based on value from third table

I have a table called Cities which is like the following:

+--------+-------------+-------+----------+------------+
| CityID |    City     | State | TimeZone | UserAccess |
+--------+-------------+-------+----------+------------+
|      1 | Albany      | NY    |        4 |          1 |
|      2 | Allentown   | PA    |        6 |          1 |
|      3 | Albuquerque | NM    |        4 |          1 |
|      4 | Amarillo    | TX    |        3 |          1 |
|      5 | Atlanta     | GA    |        4 |          1 |
+--------+-------------+-------+----------+------------+

I have another table called CitiesToRegions like the following:

+--------+----------+
| CityID | RegionID |
+--------+----------+
|      1 |       14 |
|      1 |       15 |
|      2 |       14 |
|      3 |       11 |
|      4 |       12 |
|      4 |       13 |
|      5 |       12 |
|      5 |       13 |
+--------+----------+

I have a Regions table like the following:

+----------+-----------+--------+
| RegionID |  Region   | TypeID |
+----------+-----------+--------+
|       10 | West      |      1 |
|       11 | West      |      2 |
|       12 | South     |      1 |
|       13 | South     |      2 |
|       14 | Northeast |      1 |
|       15 | Northeast |      2 |
+----------+-----------+--------+

What I'm trying to do is write a query so that I can see where I am missing a CitiesToRegions designation based upon the TypeID of the Regions table. Here is what I have so far which is returning nothing, which I'm sure is because my first join finds no nulls due to the CityID having a RegionID assigned to it. I just can't figure out how to actually write what I'm looking for.

SELECT DISTINCT Cities.CityID
FROM Cities
    INNER JOIN CitiesToRegions on Cities.CityID = CitiesToRegions.CityID
    FULL JOIN Regions on CitiesToRegions.RegionID = Regions.RegionID
WHERE (CitiesToRegions.RegionID is null) AND (Cities.UserAccess=1) AND (Regions.TypeID != 1)

What I am wanting to see for query results is the following since it is the only CityID that does not have a RegionID of Regions.TypeID = 1. 

+--------+
| CityID |
+--------+
|      3 |
+--------+

NOTE: I'm not concerned with seeing the ones missing TypeID of 2 as I would run the same query and pass in the TypeID as a variable:

Upvotes: 1

Views: 52

Answers (1)

Dale K
Dale K

Reputation: 27201

The following should do what you need - I find a not exists sub-query is clearer to get the logic right then a complex join.

select *
from Cities C
where not exists (
  select 1
  from CitiesToRegions CR
  inner join Regions R on CR.RegionID = R.RegionID and R.TypeID = 1
  where CR.CityID = C.CityID
)

Upvotes: 3

Related Questions