Getting all ending nodes in adjacency model

[kutchbhi]

My table:

$query = "CREATE TABLE categories (
    id int(11) NOT NULL auto_increment,
    name varchar(100) NOT NULL,
    parent int(11) NOT NULL,
    visible tinyint(11) NOT  NULL DEFAULT 1,
     PRIMARY KEY  (id)
)"  ;

The query:

SELECT t1.id ,t1.name FROM
flipkart_categories AS t1 LEFT JOIN categories as t2
ON t1.id = t2.parent
WHERE t2.id IS NULL AND t1.parent = 141

The idea here is to retrieve all the leaf nodes of a particular parent. But the thing is some categories are like
category > subcategory > item , whereas some are like
category > items
The above query only retrieves if there is no subcategory. Can there be a query that can handle both the above cases ?
Thanks

[kutchbhi]

Ok I got something working. but I have no idea why this works. Can someone please explain. Joins are so difficult to understand.

SELECT t3.name FROM categories AS t1 
INNER JOIN flipkart_categories as t2 ON t1.id = t2.parent
INNER JOIN flipkart_categories as t3 ON t2.id = t3.parent
WHERE   t3.parent = ? OR t2.parent = ?

[adam_k]

It seems to me that you got something working, but not all the way.
You need to replace your inner joins with left joins and you'll be set - provided that you only have 1 subcategory. Also your where could be replaced with only where t1.id = [a value here]

I don't understand why people have such a hard time understanding joins. Imagine matching 2 printouts by hand, using whatever is after ON as criteria. This is what SQL does and generates a "virtual" table as the result of this match, which contains all columns from both 2 initial tables. Your second join is the same thing, but the first of the 2 tables is a "virtual" one, that derived from the 1st join.

The reason why inner is not correct is that you don't get the categories without an item or categories with just items and no subcategories.