How to output multiple recordsets on one row using SQL?

[Guest_imported]

Hello Folks,

Using just SQL (within SQL Server 2000), I'm looking to output 2 (or more) recordsets from a one-to-many join between table all on one row. Help! Can someone please show me how to do this if it can be done.

Below is an example of the situation.

Thanks a lot folks, - Jerry


************* Tables *******************

** ITEMS **
ItemID
=======
10
11
12

** ITEMSTOPARTS **
ItemID ---- PartID ---- Funktion
=================================
10 -------- 100 ------- headlight
10 -------- 120 ------- taillight

** PARTS **
PARTID ---- Manf ---- ProductName
=================================
100 ------- M1 ------ Halogens
120 ------- M2 ------ Red Lights


************* Queries *******************

-- QUERY 1 --
==============

SELECT
- i.itemID AS ItemID,
- p1.partID AS HLPartID,
- p1.manf AS HLManf,
- p1.name AS HLName,
- p2.partID AS TLPartID,
- p2.manf AS TLManf,
- p2.name AS TLName


FROM - ZItems i
- - - - INNER JOIN ZItemsToParts ip
- - - - - - ON i.itemID = ip.itemID AND
- - - - - - (ip.funktion = 'headlight' OR
- - - - - - ip.funktion = 'taillight') AND
- - - - - - i.itemID = 10
- - - - LEFT OUTER JOIN ZParts p1
- - - - - - ON ip.partID = p1.partID AND
- - - - - - ip.funktion = 'headlight'
- - - - LEFT OUTER JOIN ZParts p2
- - - - - - ON ip.partID = p2.partID AND
- - - - - - ip.funktion = 'taillight'


************* Output *******************

Using "Query 1" I get this:
(Note: HL = Headlight; TL = Taillight)

ItemID - HLPartID - HLManf - HLName --- TLPartID - TL Manf - TL Name
================================================================
10 ----- 100 ------ M1 ----- Halogens - NULL ----- NULL ---- NULL
10 ----- NULL ----- NULL --- NULL ----- 120 ------ M2 ------ Red Lights


I wish to output the proceeding two record set in one row
Any ideas how this can be done with SQL Server 2000
(with JOINS in the FROM clause)?
- DESIRED OUTPUT:

ItemID - HLPartID - HLManf - HLName --- TLPartID - TL Manf - TL Name
===============================================================
10 ----- 100 ------ M1 ----- Halogens - 120 ------ M2 ------ Red Lights



-= End-O-Message =-

 

[jerryz]

Just don't want this post getting lost in the crowd - J

 

[tlbroadbent]

The following should work.


SELECT
i.itemID AS ItemID,
q1.partID AS HLPartID,
q1.manf AS HLManf,
q1.ProductName AS HLName,
q2.partID AS TLPartID,
q2.manf AS TLManf,
q2.ProductName As TLName

FROM zItems i

INNER JOIN

((Select ip1.ItemID, p1.partID, p1.manf, p1.ProductName
FROM zItemsToParts ip1
INNER JOIN zParts p1
ON ip1.partID = p1.partID
WHERE ip1.funktion = 'headlight') As q1

LEFT OUTER JOIN

(Select ip2.ItemID, p2.partID, p2.manf, p2.ProductName
FROM zItemsToParts ip2
INNER JOIN zParts p2
ON ip2.partID = p2.partID
WHERE ip2.funktion = 'taillight') As q2

On q1.ItemID=q2.ItemID)

ON i.itemID = q1.itemID

WHERE i.itemID = 10 Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[Guest_imported]

Tlbroadbent is a genius! His answer is correct. - Jerry

 

[Guest_imported]

Forgot to say... Terry, thanks a lot! - Jerry