Help with an SQL query please

[DarrinM]

Hi there, first request for help, an a newbee so be gentle with me !
I have :

Two tables

products & categories

products table -
productID
productcategoryID
productname

categories table -
categoryID
name
parentID

I want a query which selects each product shows it's name, it's product ID, it's product category (by name) and it's product category parent (by name). My problem is that within categories the parentID can be the categoryID and I'm not sure how to construct the Query

Example data

productID - 1001
productcategoryID - 120
productname - "200gb Hard Disk"

productID - 1002
productcategoryID - 110
productname - "R/W CD Drive"

categoryID - 100
name - Drives
parentID - Null

categoryID - 110
name - CD Drives
parentID - 100

categoryID - 120
name - Hard Disk Drives
parentID - 100

The output I would like is

1001 200gb Hard Disk Hard Disk Drives Drives
1002 R/W C/D Drive CD Drives Drives

I would appreciate your assistance

Kind Regards

Darrin

[masdi2t]

try this:

SELECT
    p.productID as ProductID,
    p.productname as ProductName,
    c.name as ProductCategory,
    pc.name as ParentProductCategory
FROM products p
LEFT JOIN categories c on p.productcategoryID = c.categoryID
LEFT JOIN categories pc on c.parentId = pc.categoryID
 

[DarrinM]

Hiya

Thanks for responding to my question about an SQL Select statement, unfortunately I wasn't specific about the actual table and field names that I am using and I am having trouble transposing the instruction you left into my DB. I have listed below the actual table names and fields, could you be kidn enough to resubmit detals using the table/field names I have shown below

Many thanks

Darrin

TABLE - ss_categories
Fields - categoryID, name, parent
TABLE ss_products
Fields - productID, categoryID, description, name, price, in_stock

I want to be able to SELECT product information (name, description etc) and show it's category name and that categories parent as well