On Thu, 08 May 2008 20:40:04 +0200, cmgmyr <cmgmyr@gmail.com> wrote:

> 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?
>
> This is kinda what i'm looking for (even though it doesn't work)
>
> 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

With minor changes:

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 p.category = COALESCE(t3.id,t2.id,t1.id);
--
Rik Wasmus