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
id id1(parent) title
1 0 parent
2 1 child1
3 1 child2
4 100 orphan
5 1 child3
6 1 child4
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