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