MySQL LEFT JOIN to self, and other table

[quasiman]

I'm quite sure my syntax is wrong, but I don't know how it should be...so I'll just show you what works and what doesn't:

 

this works:

SELECT t1.category_name AS lev1, t2.category_name AS lev2, t3.category_name AS lev3, t3.category_id AS catid
FROM categories AS t1
LEFT JOIN categories AS t2 ON t2.parent_id = t1.category_id
LEFT JOIN categories AS t3 ON t3.parent_id = t2.category_id
WHERE t1.category_name = 'KNIVES'

 

This does not work:

SELECT t1.category_name AS lev1, t2.category_name AS lev2, t3.category_name AS lev3, t3.category_id AS catid, m1.SectionCode AS mid1
FROM categories AS t1, products AS m1
LEFT JOIN categories AS t2 ON t2.parent_id = t1.category_id
LEFT JOIN categories AS t3 ON t3.parent_id = t2.category_id
WHERE t1.category_name = m1.SectionCode

 

My error says:

Unknown column 't1.category_id' in 'on clause'

 

But that doesn't really help me figure out why it's not working....

[quasiman]

Ok, after a bit of research I've given this another shot, with different results.  Now it's just timing out....

I should add in here, MySQL version 5.0.91, running this through phpMyAdmin 3.3.3.

 

 

SELECT t1.category_name AS lev1, t2.category_name as lev2, t3.category_name as lev3, t3.category_id, t1.parent_id
FROM categories AS t1
LEFT JOIN categories AS t2 ON t2.parent_id = t1.category_id
LEFT JOIN categories AS t3 ON t3.parent_id = t2.category_id
LEFT JOIN products AS m1 ON t1.category_name = m1.SectionCode
LEFT JOIN products AS m2 ON t2.category_name = m2.ClassCode
LEFT JOIN products AS m3 ON t3.category_name = m3.Family
WHERE t1.parent_id = '1';

 

By the way, I have one top level category with parent_id = '0'.  Otherwise all top levels have parent_id = '1'

[quasiman]

I figured it out...I should have used parenthesis around my tables.  This works for me:

SELECT 
t1.category_name AS lev1, 
t2.category_name AS lev2, 
t3.category_name AS lev3, 
t3.category_id AS catid, 
m1.SectionCode AS mid1
FROM (categories AS t1, products AS m1)
LEFT JOIN categories AS t2 ON t2.parent_id = t1.category_id
LEFT JOIN categories AS t3 ON t3.parent_id = t2.category_id
WHERE t1.parent_id = '1'
AND m1.Family = t3.category_name
AND m1.ClassCode = t2.category_name
AND m1.SectionCode = t1.category_name