Jump to content

Tricky Query


cmgmyr

Recommended Posts

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

Link to comment
Share on other sites

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

 

Link to comment
Share on other sites

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...

Link to comment
Share on other sites

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

 

 

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.