Return to 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?.

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?.

You could try using a LEFT OUTER JOIN:

SELECT 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   (d.device_id = 6 AND d.multiuse = 1) AND c.physical_port_id IS NULL 

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?.

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?.

You could try using a LEFT OUTER JOIN:

SELECT 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 

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?.

You could try using a LEFT OUTER JOIN:

SELECT 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 (d.device_id = 6 AND d.multiuse = 1) AND c.physical_port_id IS NULL 

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?.