MySQL Query - Count Number of Records that Don't Reference Themselves

Question

I'm still trying to use my limited knowledge of MySQL to help out a team at work, as in my last question.

The Warehouse table has a foreign key called nearest, which relates to another WarehouseID. What query generates a list of Warehouse records that are not referenced in any other Warehouse foreign key?

So running the query on:

ID    NEAREST
1     3
2     3
3     2
4     NULL

Would return:

ID
1
4

That might not seem a very useful query, but I've been trying to learn LEFT OUTER JOINs (which I'm fairly sure I need) and if I can manage this query I can easily apply it to the other tasks that are a bit harder for me to explain here.

I hope this is clear!

You can find various ways of accomplishing this in [my answer to another, similar, question](http://stackoverflow.com/questions/8103889/not-getting-expected-result-while-joining/8104784#8104784 "not getting expected result while joining"). — Andriy M, Nov 14 '11 at 12:30

score 1 · Answer 1 · answered Nov 14 '11 at 11:39

1

SELECT ID
FROM YourTable t1
LEFT JOIN YourTable t2 on t1.ID = T2.Nearest
WHERE t2.ID is null

answered Nov 14 '11 at 11:39

Andrew

26,629
5
63
86

score 0 · Answer 2 · answered Nov 14 '11 at 13:18

0

another way:

SELECT *
FROM YourTable
WHERE ID NOT IN (SELECT DISTINCT NEAREST
                 FROM YourTable);

answered Nov 14 '11 at 13:18

pierroz

7,653
9
48
60

MySQL Query - Count Number of Records that Don't Reference Themselves

2 Answers2