Complex Query

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

Thanks in advance,
-Chris

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

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?

On Thu, 08 May 2008 15:08:31 -0700, cmgmyr wrote:

> On May 8, 4:39Â*pm, "Rik Wasmus" <luiheidsgoe...@hotmail.com> wrote:
>> 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);
>
> 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?

Use SELECT DISTINCT rather than SELECT

--
Remove caps to reply

On May 9, 1:45*pm, PleegWat <pleegwat.REM...@CAPS.telfort.nl.INVALID>
wrote:
> On Thu, 08 May 2008 15:08:31 -0700, cmgmyr wrote:
> > On May 8, 4:39*pm, "Rik Wasmus" <luiheidsgoe...@hotmail.com> wrote:
> >> 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);
>
> > 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?
>
> Use SELECT DISTINCT rather than SELECT
>
> --
> Remove caps to reply

Thanks for the reply. I did try that with some other alterations:

SELECT DISTINCT(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)
GROUP BY(p.id)
ORDER BY p.id

This is coming up with 604 records total (there are actually 607
products) I did some looking and it's not pulling any items that are
assigned to a sub-category with sub-sub-categories for some reason.

Category 1
- Category 2 <~~~ Not pulling these products
-- Category 3

Is there any reason for this?

Thanks!
-Chris