Retrieve records from 2 tables

snufse · January 28th, 2008, 09:36 AM

I need to retrieve data from 2 tables (I have a sp that populates each table)regardless if they have a match or not.

First table:
#JobListTable
job_date datetime,
job_number char(15),
job_phase char(15),
qty_delivered decimal(8,2)

Second table:
#EquentialJobListTable
job_date datetime,
job_number char(15),
job_phase char(15),
qty_received decimal(8,2)

There could be a match between the two tables, or a record could exist in one and not in the other. The matching key will be the first 3 fields from each table. Result will be loaded into a DataSet and DataGrid

This is what I have. Not sure if it is correct approach.

SELECT #JobListTable.job_date, #JobListTable.job_number, #JobListTable.job_phase, #JobListTable.qty_delivered, #EquentialJobListTable.job_date, #EquentialJobListTable.job_number, #EquentialJobListTable.job_phase, #EquentialJobListTable.qty_received
FROM #JobListTable FULL OUTER JOIN #EquentialJobListTable
ON #JobListTable.job_date = #EquentialJobListTable.job_date and #JobListTable.job_number = #EquentialJobListTable.job_number and #JobListTable.job_phase = #EquentialJobListTable.job_phase

Regardless of match or not, I only want to to see job_date, job_number and job_phase one time and not from both tables

joefawcett · January 28th, 2008, 11:55 AM

I think you need a union of a left join and a right join:

Code:

SELECT * FROM #JobListTable JL
LEFT OUTER JOIN #SequentialJobListTable SJL
ON JL.job_date = SJL.job_date AND
JL.job_number = SJL.job_number AND
JL.job_phase = SJL.job_phase

UNION

SELECT * FROM #JobListTable JL
RIGHT OUTER JOIN #SequentialJobListTable SJL
ON JL.job_date = SJL.job_date AND
JL.job_number = SJL.job_number AND
JL.job_phase = SJL.job_phase

although you haven't stated exactly what results you need so it's hard to say.

--

Joe (Microsoft MVP - XML)

Jeff Mason · January 28th, 2008, 12:21 PM

A FULL OUTER JOIN would also work, and avoid the UNION operation.

Jeff Mason
[email protected]

joefawcett · January 28th, 2008, 12:59 PM

Thanks Jeff, never seen that one before.

--

Joe (Microsoft MVP - XML)

Jeff Mason · January 28th, 2008, 01:22 PM

Although, now that I think about it, the two alternatives aren't equivalent.

The UNION query would return one set of rows for the "left" side followed by another set for the "right".

The FULL OUTER JOIN would return essentially half as many rows, with either the "left" side values or "right" side values NULL depending on which didn't exist in their corresponding table.

Not quite the same thing...

Jeff Mason
[email protected]

joefawcett · January 28th, 2008, 01:51 PM

Yes, but the lack of ALL in the UNION would mean duplicates would be eliminated, wouldn't that lead to the same thing?
So if the two tables were:

Code:

JobListTable
job_date    job_number    job_phase    qty_delivered
20080101            1            1            1
20080102            1            1            1
20080103            2            1            1
20080104            3            1            1

SequentialJobListTable
job_date    job_number    job_phase    qty_received
20080101            1            1            1
20080102            2            2            1
20080103            2            1            1
20080101            4            1            1

The UNION version would be:

Code:

job_date    job_number    job_phase    qty_delivered    job_date    job_number    job_phase    qty
20080101            1            1            1        20080101            1            1            1
20080102            1            1            1        NULL            NULL            NULL            NULL
20080103            2            1            1        20080103            2            1            1
20080104            3            1            1        NULL            NULL            NULL            NULL
20080101            1            1            1        20080101            1            1            1
NULL            NULL            NULL        NULL        20080102            2            2            1
20080103            2            1            1        20080103            2            1            1
NULL            NULL            NULL        NULL        20080101            4            1            1

Rows 1 and 5 and 3 and 7 are duplicates so rows 5 and 7 would be removed.

--

Joe (Microsoft MVP - XML)

Jeff Mason · January 28th, 2008, 01:59 PM

You are right. I totally blew that.

I should have looked closer at the UNION query and noticed that there were OUTER joins in there, so the number of columns would be the same in either case. My bad.

Jeff Mason
[email protected]

joefawcett · January 29th, 2008, 04:31 AM

No, I'm glad, you started me thinking about it and I now think I have it sorted in my head :)

--

Joe (Microsoft MVP - XML)