CODEWITHSUNDEEP
mysqlsqlforeign-keys
Max V Z
Max V Z

Reputation: 61

Foreign key in same table, unable to get right query

I've got a very simple table that looks something like this. Iet's call the table 'family' the columns are ID, name, age and parent so: 

ID    Name      Age     Parent
------------------------------
1     Bob        50      NULL
2     Matt       20        1  
3     Rick       18        1

ID is the primary key and Parent is a foreign key referencing ID What I'm trying to do is to list every parents ID and name. My current query looks like this:

Select ID, Name
from family
where ID = Parent;

But this is comes up with no results. How is my query incorrect? Shouldn't it show all records for all the parent ID's found? Thanks! The result that I am looking for would look like this:

ID    Name    
--------------
1     Bob

And if the table was larger and had multiple parents, they would of course be listed in the result as well.

Upvotes: 1

Views: 60

Answers (1)

Uueerdo
Uueerdo

Reputation: 15941

Your query attempt does not compare rows against each other, this is (posssibly) the simplest:

SELECT ID, Name
FROM family
WHERE ID IN (SELECT Parent FROM family)
;

This alternative MIGHT be faster:

SELECT DISTINCT p.ID, p.Name
FROM family AS p
INNER JOIN family AS c ON p.ID = c.Parent
;

Upvotes: 1

Related Questions