On May 8, 4:39*pm, "Rik Wasmus" <luiheidsgoe...@hotmail.com> wrote:
> On Thu, 08 May 2008 20:40:04 +0200, cmgmyr <cmg...@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- Hide quoted text -
>
> - Show quoted text -

Thank you very much for that. It's very close. The only problem is, is
that if an item has 2 categories it shows as 2 rows, if it has 3
categories, it shows 3 times. Any ideas?