Selecting columns from multiple tables

[10basetom]

Hello,

Given the following tables:

SKU Table
OID NAME
=== ================
1   IBM ThinkPad T23
2   Dell GX150
3   Compaq Evo D510

CAT Table (PARENT_OID->CAT.OID)
OID NAME      PARENT_OID
=== ========= ==========
1   Computers NULL
2   Notebooks 1
3   Desktops  1

MAP Table (OID->SKU.OID, PARENT_OID->CAT.OID)
OID PARENT_OID
=== ==========
1   2
2   3
3   3

I'm trying to write a SQL query that returns this result set:

PRODUCT NAME     CATEGORY  PARENT CATEGORY
================ ========= ===============
IBM ThinkPad T23 Notebooks Computers
Dell GX150       Desktops  Computers
Compaq Evo D510  Desktops  Computers

I know how to join all three tables to return the first two columns, but I'm struggling to use the PARENT_OID value from the CAT table to retrieve the category name of that category's parent category and then return it in the same result set. Any ideas?

TIA,
Tom

 

[PHV]

Something like this ?
SELECT S.Name, C.Name, P.Name
FROM SKU S
INNER JOIN MAP M ON S.OID=M.OID
INNER JOIN CAT C ON M.Parent_OID=C.OID
INNER JOIN CAT P ON C.Parent_OID=P.OID

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[10basetom]

hi PHV,

thanks so much for leading me to the light! i had a doh! moment as soon as i read your response -- i didn't realize that you could join the same table twice under different names. learning is so much fun.

thanks again,
tom