CODEWITHSUNDEEP
sqlsql-server
Ctrl_Alt_Defeat
Ctrl_Alt_Defeat

Reputation: 4009

SQL Query to return rows with no matches in another table

I have a users country table where I hold a list of users country's- a user can have many addresses and therefore many country's

it is joined to the user table via an address table

if I do a select distinct CountryId from UserCountry Data returned is:

1
2
3
4
5
6
etc

If I run the following query I get a User returned

select * from User u join Address a
on u.AddressId = a.Id
where a.CountryId = 1

If I then run 

select * from User u join Address a
on u.AddressId = a.Id
where a.CountryId = 2

I get no data returned which is fine. What I need to to do is pass in a list of all the distinct Country Ids and produce a output of which set return a User Object and which set don't return a User object

Upvotes: 0

Views: 115

Answers (2)

Giorgos Betsos
Giorgos Betsos

Reputation: 72175

The following query:

SELECT CountryId, COALESCE(t2.cnt, 0) AS usersPerCountry 
FROM (
   SELECT DISTINCT CountryId 
   FROM UserCountry) AS t1
LEFT JOIN (
   SELECT a.CountryId, COUNT(*) AS cnt
   FROM User u 
   INNER JOIN Address a ON u.AddressId = a.Id
   GROUP BY a.CountryId
) AS t2 ON t1.CountryId = t2.CountryId
ORDER BY COALESCE(t2.cnt, 0) DESC

will give you the number of users associated with each country. If a country is not related to any users at all then 0 is returned.

Explanation:

The above query makes use of two derived tables, t1 and t2:

  • The first derived table of the query, t1, contains a list of all distinct CountryId values contained in UserCountry.
  • The second derived table, t2, returns the number of users per CountryId. Ids with no users are not returned by this sub-query.

Performing a LEFT JOIN between t1 and t2 with t1 as the first table returns all values of t1, i.e. all CountryId values contained in UserCountry. If there is no match in t2, then t2.cnt is NULL. COALESCE used in the SELECT clause converts this NULL value into a 0.

Upvotes: 1

gmiley
gmiley

Reputation: 6604

This will give you all items in User and Address that do not have a Country entry. Is this what you are looking for?

SELECT *
FROM User U
JOIN Address a
ON a.Id = u.AddressId
WHERE NOT EXISTS
(
   SELECT 1
   FROM UserCountry uc
   WHERE uc.CountryId = a.CountryId
);

For the list of Country items that do not have a User, you can alternate the statements and find where the CountryId NOT EXISTS in the User and Address tables

Upvotes: 0

Related Questions