Selecting columns from multiple tables

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Selecting columns from multiple tables

Author

Topic

10basetom
Starting Member

2 Posts

Posted - 2006-12-09 : 18:04:04

Hello,

Given the following tables:


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

CAT Table
OID NAME      PARENT_OID
=== ========= ==========
1   Computers NULL
2   Notebooks 1
3   Desktops  1

MAP Table
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

10basetom
Starting Member

2 Posts

Posted - 2006-12-09 : 18:07:42

I forgot to clarify the following:

- the PARENT_OID column in the CAT table links back to the OID column in the CAT table
- the OID column in the MAP table links to the OID column in the SKU table
- the PARENT_OID column in the MAP table links to the OID column in the CAT table

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-11 : 03:54:59

[code]declare @SKU Table (OID int, NAME varchar(100))

insert @sku
select 1, 'IBM ThinkPad T23' union all
select 2, 'Dell GX150' union all
select 3, 'Compaq Evo D510'

declare @CAT Table (OID int, NAME varchar(100), PARENT_OID int)

insert @cat
select 1, 'Computers', NULL union all
select 2, 'Notebooks', 1 union all
select 3, 'Desktops', 1

declare @MAP Table (OID INT, PARENT_OID INT)

INSERT @MAP
SELECT 1, 2 union all
SELECT 2, 3 union all
SELECT 3, 3

SELECT p.Name [Product Name],
c1.Name [Category],
c2.Name AS [Parent Category]
FROM @SKU p
LEFT JOIN @MAP m ON m.OID = p.OID
LEFT JOIN @CAT c1 ON c1.OID = m.Parent_OID
LEFT JOIN @CAT c2 ON c2.OID = c1.Parent_OID[/code]

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources