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
– plain jane, Commented Aug 23, 2013 at 10:55
your first table is MyISAM and second table InnoDB.. i hope that is not making a difference.. — plain jane
– plain jane, Commented Aug 23, 2013 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
– martincarlin87, Commented Aug 23, 2013 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
– martincarlin87, Commented Aug 23, 2013 at 11:11

Community · Accepted Answer · 2017-05-23 12:23:33Z

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, 2017 at 12:23

CommunityBot

11 silver badge

answered Aug 23, 2013 at 11:00

Drew MacInnis

8,6271 gold badge24 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

martincarlin87 Over a year ago

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

Drew MacInnis Over a year ago

Ok. I edited my answer to use your WHERE clause. I added a DISTINCT, does that solve your repeated row?

Strawberry · Accepted Answer · 2013-08-23 11:00:40Z

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;

Collectives™ on Stack Overflow

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

2 Answers 2

2 Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Linked

Related