MySQL - Selecting rows who's id is not present as a foreign key in another table

Question

In my rails app I have two tables - `device_ports` and `circuits`. My goal is to get a list of `device_ports` whose `id` is not being used in the `physical_port_id` column of the `circuits` table. I have done something similar before on other tables but here my query only returns one row when it should return 23 rows - there are 24 device ports for this device and one is in use. ``` select id, name, device_id, multiuse from device_ports where (device_id = 6 and multiuse = 1) or device_ports.id not in (select physical_port_id from circuits) ``` So this query gets all multiuse ports (so even if the id was referenced in the foreign key, this row should still be returned) and should also get all rows where the device_id is 6 but is not referenced in circuits but only the multiuse row is being returned. The result from the query is ``` id | name | device_id | multiuse ------------------------------------ 268 | test-1 | 6 | 1 ``` I did try to create an sql fiddle but the build just seems to…

Accepted Answer

You could try using a LEFT OUTER JOIN:

```
SELECT DISTINCT d.id, d.name, d.device_id, d.multiuse
 FROM device_ports d
 LEFT OUTER JOIN circuits c ON c.physical_port_id = d.id 
WHERE 
 (c.physical_port_id IS NULL AND d.device_id = 6) 
 OR (d.multiuse = 1 AND d.device_id = 6) 
 ORDER BY d.id 
```

There are several techniques for this query, take a look at What's the difference between NOT EXISTS vs. NOT IN vs. LEFT JOIN WHERE IS NULL?.

MySQL - Selecting rows who's id is not present as a foreign key in another table

Solution

Problem

Related problems