Tricky Query

[cmgmyr]

Hey All,

I have a little delema that I can't figure out. I have this query currently:

SELECT p.id, p.styleno, t1.name AS category1, t2.name as category2, t3.name as category3, p.description, p.price, p.sort 
FROM 
categories AS t1
LEFT JOIN 
categories AS t2 ON t2.parentid = t1.id
LEFT JOIN 
categories AS t3 ON t3.parentid = t2.id
INNER JOIN 
products AS p ON t3.id = p.category

 

Now, this works great if there are 3 levels of categories, but I need this to be altered to show all of the items if there are 1, 2, or 3 levels of categories...and not just 3. Any ideas?

 

Thanks in advance,

-Chris

[rhodesa]

This webpage is great and talks ALL about it: http://dev.mysql.com/tech-resources/articles/hierarchical-data.html

[cmgmyr]

Thanks for the link, actually that's where I got the query initially then added the other table into it. But that still doesn't solve the problem.

[rhodesa]

I just reread your query, and the logic doesn't make much sense. What do you want your result set to look like?

[cmgmyr]

It's an export of a products table. I want it to show all of the data from the products table along with the names of the categories (and subs if available) instead of the category id.

[rhodesa]

hum...try this:

 

SELECT p.id, p.styleno, t1.name AS category1, t2.name as category2, t3.name as category3, p.description, p.price, p.sort 
FROM products AS p
LEFT JOIN categories AS t1 ON t1.id = p.category
LEFT JOIN categories AS t2 ON t2.parentid = t1.id
LEFT JOIN categories AS t3 ON t3.parentid = t2.id

 

[cmgmyr]

no, that wouldn't work. p.category could be t1.id, t2.id, or t3.id I'm looking for something like this:

SELECT p.id, p.styleno, t1.name AS category1, t2.name as category2, t3.name as category3, p.description, p.price, p.sort 
		FROM 
			categories AS t1
		LEFT JOIN 
			categories AS t2 ON t2.parentid = t1.id
		LEFT JOIN 
			categories AS t3 ON t3.parentid = t2.id
		IF t3.id <> NULL INNER JOIN products AS p ON t3.id = p.category
		ELSE IF t2.id <> NULL INNER JOIN products AS p ON t2.id = p.category
		ELSE INNER JOIN products AS p ON t1.id = p.category

which obviously that doesn't work...

[rhodesa]

Well, I'm glad I just wasted 10 minutes creating tables and adding sample data just to find that my query DID work...

 

If the item is in a top level category, category 1 will be it's category, and 2 and 3 will be NULL. If it's one level down, 1 will be it's category, 2 will be the parent (top level) and 3 will be NULL, etc

 

 

[cmgmyr]

Well it kinda worked but it's not the results that I wanted. I want it to show the top level category, then the sub-cat (if available) then the sub-sub-cat (if available) in the same row.

[fenway]

Display issues should be handled at the php level.

[cmgmyr]

Yes, I know. I made a function to take a query and export that to an Excel file, so that's why i need it in one query.

[fenway]

Yes, I know. I made a function to take a query and export that to an Excel file, so that's why i need it in one query.

Could you post your current query, some same output, and the desired output?