How do I return data from 2 different rows on the same row?

[jenabl01]

When I run the following, I get two rows of data for the same unit, invoice, line_num that has two spcl_bi_id and spcl_bi_txt.

SELECT A.UNIT, A.INVOICE, A.LINE_NUM, B.SPCL_BI_ID, B.SPCL_BI_TXT
FROM BI_LINE A, BI_LINE_SB B
WHERE A.UNIT = B.UNIT
AND A.INVOICE = B.INVOICE
AND A.LINE_NUM = B.LINE_NUM

Is there a way to return only one row for each unit, invoice, line_num with the two spcl_bi_id and spcl_bi_txt on the the same line?

Thanks,
Jen

 

[tlbroadbent]

Create a query for each condition and then JOIN the two queries.

SELECT
A.UNIT, A.INVOICE,
A.LINE_NUM,
A.SPCL_BI_ID AS SPCL_BI_IDA,
A.SPCL_BI_TXT AS SPCL_BI_TXTA,
B.SPCL_BI_ID AS SPCL_BI_IDB,
B.SPCL_BI_TXT AS SPCL_BI_TXTB

FROM

(SELECT
UNIT, INVOICE, LINE_NUM,
SPCL_BI_ID, SPCL_BI_TXT
FROM BI_LINE
WHERE <criteria to identify 1st row>) AS A

INNER JOIN

(SELECT
UNIT, INVOICE, LINE_NUM,
SPCL_BI_ID, SPCL_BI_TXT
FROM BI_LINE
WHERE <criteria to identify 2nd row>) AS B

ON A.UNIT = B.UNIT
AND A.INVOICE = B.INVOICE
AND A.LINE_NUM = B.LINE_NUM
Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.