Baldráni Baldráni - 5 months ago 13
MySQL Question

Select * where association is empty in SQL?

So I'm a bit lost in SQL and especially in the right syntax to use.

To be quick : This is my tables

We have products :

+------------------------+------------------+------+-----+---------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------------------+------------------+------+-----+---------------------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| slug | varchar(255) | NO | | NULL | |
| title | varchar(255) | NO | | NULL | |
| resume | varchar(255) | NO | | NULL | |
| country_id | varchar(255) | NO | | NULL | |
| city_id | varchar(255) | NO | | NULL | |
+------------------------+------------------+------+-----+---------------------+----------------+


And we have cities :

+------------------+------------------+------+-----+---------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------------+------------------+------+-----+---------------------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| country_id | int(11) | NO | | NULL | |
| name | varchar(255) | NO | | NULL | |
+------------------+------------------+------+-----+---------------------+----------------+


What I want to find is which cities does not have products.

So basically I tried something like this :


SELECT name FROM cities WHERE COUNT (SELECT * FROM cities INNER JOIN
products ON products.city_id = cities.id) = 0;


I think I have a misunderstanding of how does work count, can I have a bit of help on this one pls?

Answer

You can do it with NOT EXISTS:

SELECT name 
FROM cities AS c
WHERE NOT EXISTS (SELECT 1
                  FROM products AS p
                  WHERE p.city_id = c.id)

The above query returns the names of all cities not being linked to a product.