Multiple Join - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

Multiple Join

Author

Topic

mccallug
Starting Member

6 Posts

Posted - 2008-07-25 : 12:44:35

I am trying to build a DB for keeping track of serial numbers within assemble parts. In the example below the computers table has multiple part ids(Motherboard, VideoCard, and HardDrive) that each have their own entry int the Inventory table. What would be the best way to retieve the SerialNumber information for each part in the Computers table from the related record in the Inventory table in a single select statement?

Example: I want to no the serial numbers of parts inside a computer.

Table:Computers Table:Inventory
ComputerID int, InventoryID int,
MotherboardID int, PartNumber varchar(15),
VideoCardID int, SerialNumber varchar(15)
HardDriveID int,
.
.
.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-25 : 13:44:12

try like this

SELECT t.ComputerID,i.SerialNumber
FROM
(
SELECT ComputerID,IDValues
FROM
(
SELECT * FROM Computers 
)m
UNPIVOT (IDValues FOR ID IN ([MotherBoardID],[VideoCardID],[HardDriveID]))p
)t
INNER JOIN Inventory i
ON i.InventoryID=t.IDValues

mccallug
Starting Member

6 Posts

Posted - 2008-07-25 : 14:46:01

Thanks for the suggestion. Unfortunately it isn't quite what I am looking for. That returns the serial numbers as seperate rows in a datatable. I need them all to be in a single row.

For example

ComputerID ComputerSN MotherboardID MotherboardSN VideoCardID VideoCardSN HardDriveID HardDriveSN
1_________ ABC_______ 2____________ DEF__________ 3__________ GHI________ 4__________ JKL

You code would return the following

ComputerID SerialNumber
1_________ ABC
1_________ DEF
1_________ GHI
1_________ JKL

Anymore suggestions? Thanks again!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources