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 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);

i tried at my end the code pasted above it is giving me 25 records.. — plain jane, Aug 23 '13 at 10:55
your first table is MyISAM and second table InnoDB.. i hope that is not making a difference.. — plain jane, Aug 23 '13 at 10:57
@Sonali well spotted... not sure if that would have an impact or not, 25 sounds correct since that should give you 23 results from device 6 and the two from device 1? I forgot to add `AND device_id = 6` to the query to filter those out. — martincarlin87, Aug 23 '13 at 10:59
@Sonali hmm, the different engines must be having an effect then.Using Drew's original answer I can get close to what I am looking for despite the difference. — martincarlin87, Aug 23 '13 at 11:11
am astonished at this fact that different engines have an effect on query so much... — plain jane, Aug 23 '13 at 11:15

score 7 · Accepted Answer · edited May 23 '17 at 12:23

7

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

edited May 23 '17 at 12:23

Community

1
1

answered Aug 23 '13 at 11:00

Drew MacInnis

8,267
1
22
18

I am close with your original answer but the edit just gives me the one row again. This is my latest attempt: `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 AND d.device_id = 6) OR (d.multiuse = 1 AND d.device_id = 6) ORDER BY id ASC` which is almost perfect, the only 'bug' is that if a multiuse port IS references it will appear twice in the result. Could probably just group on id but would like the query to be smarter – martincarlin87 Aug 23 '13 at 11:09
Ok. I edited my answer to use your WHERE clause. I added a DISTINCT, does that solve your repeated row? – Drew MacInnis Aug 23 '13 at 11:20

score 0 · Answer 2 · answered Aug 23 '13 at 11:00

0

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;

answered Aug 23 '13 at 11:00

Strawberry

33,750
13
40
57

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

2 Answers2