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

```
CREATE TABLE `device_ports` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `device_id` int(11) DEFAULT NULL,
  `name` tinytext,
  `speed` tinytext,
  `multiuse` tinyint(1) DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `id` (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=291 DEFAULT CHARSET=latin1;

INSERT INTO `device_ports` (`id`, `device_id`, `name`, `speed`, `multiuse`, `created_at`, `updated_at`)
*emphasized text*VALUES
(1, 1, 'Test Device Port', '100', 0, NULL, NULL),
(2, 1, 'Test Port 2', '300', 1, NULL, NULL),
(289, 6, 'test-22', '100', 0, NULL, NULL),
(290, 6, 'test-23', '100', 0, NULL, NULL),
(288, 6, 'test-21', '100', 0, NULL, NULL),
(287, 6, 'test-20', '100', 0, NULL, NULL),
(286, 6, 'test-19', '100', 0, NULL, NULL),
(284, 6, 'test-17', '100', 0, NULL, NULL),
(285, 6, 'test-18', '100', 0, NULL, NULL),
(283, 6, 'test-16', '100', 0, NULL, NULL),
(282, 6, 'test-15', '100', 0, NULL, NULL),
(281, 6, 'test-14', '100', 0, NULL, NULL),
(280, 6, 'test-13', '100', 0, NULL, NULL),
(279, 6, 'test-12', '100', 0, NULL, NULL),
(278, 6, 'test-11', '100', 0, NULL, NULL),
(277, 6, 'test-10', '100', 0, NULL, NULL),
(276, 6, 'test-9', '100', 0, NULL, NULL),
(275, 6, 'test-8', '100', 0, NULL, NULL),
(274, 6, 'test-7', '100', 0, NULL, NULL),
(273, 6, 'test-6', '100', 0, NULL, NULL),
(272, 6, 'test-5', '100', 0, NULL, NULL),
(271, 6, 'test-4', '100', 0, NULL, NULL),
(270, 6, 'test-3', '100', 0, NULL, NULL),
(269, 6, 'test-2', '100', 0, NULL, NULL),
(268, 6, 'test-1', '100', 1, NULL, NULL),
(267, 6, 'test-0', '100', 0, NULL, NULL);

CREATE TABLE `circuits` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `organisation_id` int(11) DEFAULT NULL,
  `physical_port_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=248 DEFAULT CHARSET=latin1;

INSERT INTO `circuits` (`id`, `organisation_id`, `physical_port_id`)
VALUES (1, 125, 267);

```
['https://Stackoverflow.com/questions/18400673', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/634120/']
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?](https://stackoverflow.com/questions/2246772/whats-the-difference-between-not-exists-vs-not-in-vs-left-join-where-is-null).
```
SELECT p.* 
  FROM device_ports p 
  LEFT 
  JOIN circuits c 
    ON c.physical_port_id = p.id 
 WHERE p.device_id = 6 
   AND multiuse = 1 
   AND c.id IS NULL;

```