Jump to content

Tricky Query


cmgmyr
 Share

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.

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.

 Share

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