Help! Select query nested probably 1

[pc77]

I am stuck!
Please help me out..

I am using this query right now, to produce a list of main categories.
All main categories have the value 0 in ParentID.

SELECT CatID, CatTitle, ParentID
FROM cats
WHERE ParentID = '0'
ORDER BY CatTitle ASC

This query produce a return like:
5,Cars, 0,
6,Cats, 0
and so on..

Now i would like to list main category with some subcategories underneath.
Like this:
Cars
BMW, MB, Rover, Pontiac

All subcategories have value of the main categories CatID as ParentID, but i only want to show the subcategories with a value of 1 in a field called Products.

My table looks like this:

CatID | CatTitle | ParentID | Products
----------------------------------------
5, Cars, 0, 0
6, Cats, 0, 0
7, BMW, 5, 1
8, MB, 5, 1
9, VW, 5, 0
10, Bentley 5, 0
11, Pontiac, 5, 1

How will a query to accomplish this look like?

 

[r937]

SELECT parent.CatID
     , parent.CatTitle
     , group_concat(product.CatTitle) as products
  FROM cats as parent 
INNER
  JOIN cats as product
    ON product.ParentID = parent.CatID
   AND product.Products = 1
 WHERE parent.ParentID = 0 
GROUP
    BY parent.CatID
     , parent.CatTitle
ORDER 
    BY parent.CatTitle ASC

r937.com | rudy.ca

 

[pc77]

Thanks r937 for your reply.
Your code works fine, but i don't get the data i need.
I know you can solve this.

My first question was not clear enough.. Here comes a clarification:

I need to get data output like this:

CatTitel, CatID (for the main category where parentID = 0)
CatTitle, CatID (for those subcategory that has a value of 1 in field Product)

The output should look like this:
Cars
CatID

BMW, MB, Rover, Pontiac
CatID, CatID, CatID, CatID
(Both main- and individual subcategories should be links, that's why I need CatID for every subcategory as well.

Hope you can help me out again.

 

[r937]

now i'm not sure what you want

i thought you wanted GROUP_CONCAT because it produces a single comma-delimited string of values

i don't understand your new "re-arrangement" of data

why can't you do the formatting in your scripting language (php presumably)?

r937.com | rudy.ca

 

[pc77]

Hi r937,
thanks for helping me out.

I'l show you an example of what im trying to accomplish:

http://shopping.yahoo.com/

There you can among others see:
Clothing & Accessories
Women's, Men's, Shoes, more...

Every main categoy and subcategory are linked to another page, thats what im intending.
(I'm writing code in ASP)
Do you need any more info jus say so.

 

[r937]

SELECT parent.CatID
     , parent.CatTitle
     , GROUP_CONCAT(
          CONCAT(product.CatID,'|',product.CatTitle) 
                   ) AS products
  FROM ...

this will give you values like this --

7|BMW,8|MB,11|Pontiac,etc.

then you can explode on the commas, and for each item, explode on the pipe to get the id and the name

r937.com | rudy.ca