CODEWITHSUNDEEP
mysqlsql
steel
steel

Reputation: 12520

SQL: Return only records without any associations that meet criteria

How can I return only the records that do not have any associated records that meet a certain criteria?

For example, if I have a one to many relationship between users and addresses, how can I get all the users who do NOT have a specific city in their address history?

SQL Fiddle here

Example data here:

CREATE TABLE Users
  (`id` int, `name` varchar(20))
;

CREATE TABLE Addresses
  (`id` int, `city` varchar(20),`user_name` varchar(20))
;

INSERT INTO Users
  (`id`, `name`)

VALUES
  (1, 'sarah'),
  (2, 'harry'),
;

INSERT INTO Addresses
  (`id`, `city`, `user_name`)

VALUES
  (1, 'denver', 'sarah'),
  (2, 'anchorage', 'sarah'),
  (3, 'providence', 'harry'),
  (4, 'new york', 'harry')
;

Upvotes: 2

Views: 458

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269633

The simplest way is with not exists or left join:

select u.*
from users u left join
     addresses a
     on a.username = u.username and
        a.city = 'Peoria'
where a.city is null;

The left join keeps all records in users and any records in addresses that match the on conditions. In this case (because the city name is in the on condition), it returns all users with either information about the cities or NULL values. The where clause chooses the NULL values -- the non-matching ones.

The equivalent not exists might be easier to follow:

select u.*
from users u 
where not exists (select 1
                  from addresses a
                  where a.username = u.username and
                        a.city = 'Peoria'
                 );

Upvotes: 2

Related Questions