CODEWITHSUNDEEP
mysql
John Mc Murray
John Mc Murray

Reputation: 363

Find rows in one table which don't appear multiple times in link table

I have multiple sites and when a user registers on one site we automatically create an account for them on several sites.

Our "central" database which manages this has a clients table, a sites table and then a clients_sites table as the link.

clients table
-------------
id | name
1  | Larry
2  | Curly

 

sites table
-----------
id | url
1  | http://one.com
2  | http://two.com

 

clients_sites
--------------
id | clients_id | sites_id
1  | 1          | 1
2  | 1          | 2
3  | 2          | 1

I need to find an efficient query to find clients who aren't listed in the link table for each site.

So in this example Curly is not listed in site two.

Is there a single SQL query to find clients where they don't appear in the link table for each site?

Upvotes: 1

Views: 28

Answers (1)

Madhur Bhaiya
Madhur Bhaiya

Reputation: 28834

  • We can consider all possible combinations of a clients_id with sites_id, using Cross Join in a Derived Table.
  • Now, we can use an "anti-join" to consider only those "combinations" where there is no matching row found in the clients_sites table, using Left Join and clients_sites.id IS NULL (no matching row)

Try the following query:

SELECT dt.* 
FROM 
(SELECT c.id AS clients_id, 
        c.name AS clients_name, 
        s.id AS sites_id, 
        s.url AS sites_url 
 FROM clients AS c 
 CROSS JOIN sites AS s 
) AS dt 
LEFT JOIN clients_sites AS cs 
  ON cs.clients_id = dt.clients_id AND 
     cs.sites_id = dt.sites_id 
WHERE cs.id IS NULL

Upvotes: 1

Related Questions