Klaaz Klaaz - 7 months ago 28
SQL Question

Mysql only select row with existing parent

I have this query:

SELECT id,id1,title FROM tablename
LEFT JOIN tablename AS parent
ON tablename .id1 = parent.id
WHERE parent.id is NULL


What I try to achieve is that only rows are shown from which no parent exist.

Table layout and content

id id1(parent) title
1 0 parent
2 1 child1
3 1 child2
4 100 orphan
5 1 child3
6 1 child4


In this example I would query all but leave out the one which has no existing parent row (row 4, the orphan, parent 100 which does not exists in tablename).

Answer

This

SELECT child.id,child.id1,child.name 
FROM table AS child --alias names, as column names would be ambiguous
LEFT JOIN table AS parent 
    ON table.id1 = parent.id 
WHERE parent.id is NULL  -- only no parent!

Shows the orphans. To get all non-orphans

SELECT child.id,child.id1,child.name 
FROM table AS child
JOIN table AS parent -- JOIN takes care of getting only the records with parents 
    ON child.id1 = parent.id 

Why does this work this way? LEFT JOIN is for joining tables, where we would like to receive rows of the left hand side table, where the right hand side table does not have a record that fulfills the join criteria. The columns pertaining to the right hand side table would all be NULL in that case. By using a simple JOIN, only those rows are shown from the first table, that have a record in the table on right hand side of the join.

Why did you get wrong result

The column names can get to be ambiguous when joining tables, and always do so when self-joining... You have to distinguish between them, by using alias names.

Ideas to consider

Maintainability. Keep this in mind, even for examples. Name your objects properly: table is not a descriptive name (and is a keyword too), use PERSON instead. For columns, id1 is not a descriptive name - use PARENT_ID instead...

Comments