Wrox Programmer Forums
Go Back   Wrox Programmer Forums > SQL Server > SQL Server 2000 > SQL Server 2000
| Search | Today's Posts | Mark Forums Read
SQL Server 2000 General discussion of Microsoft SQL Server -- for topics that don't fit in one of the more specific SQL Server forums. version 2000 only. There's a new forum for SQL Server 2005.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2000 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old January 28th, 2008, 09:36 AM
Friend of Wrox
 
Join Date: Mar 2007
Location: West Palm Beach, Florida, USA.
Posts: 205
Thanks: 4
Thanked 0 Times in 0 Posts
Default Retrieve records from 2 tables

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
 
Old January 28th, 2008, 11:55 AM
joefawcett's Avatar
Wrox Author
Points: 9,763, Level: 42
Points: 9,763, Level: 42 Points: 9,763, Level: 42 Points: 9,763, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Exeter, , United Kingdom.
Posts: 3,074
Thanks: 1
Thanked 38 Times in 37 Posts
Default

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)
 
Old January 28th, 2008, 12:21 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: Hudson, MA, USA.
Posts: 839
Thanks: 0
Thanked 1 Time in 1 Post
Default

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

Jeff Mason
je.mason@comcast.net
 
Old January 28th, 2008, 12:59 PM
joefawcett's Avatar
Wrox Author
Points: 9,763, Level: 42
Points: 9,763, Level: 42 Points: 9,763, Level: 42 Points: 9,763, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Exeter, , United Kingdom.
Posts: 3,074
Thanks: 1
Thanked 38 Times in 37 Posts
Default

Thanks Jeff, never seen that one before.

--

Joe (Microsoft MVP - XML)
 
Old January 28th, 2008, 01:22 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: Hudson, MA, USA.
Posts: 839
Thanks: 0
Thanked 1 Time in 1 Post
Default

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
je.mason@comcast.net
 
Old January 28th, 2008, 01:51 PM
joefawcett's Avatar
Wrox Author
Points: 9,763, Level: 42
Points: 9,763, Level: 42 Points: 9,763, Level: 42 Points: 9,763, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Exeter, , United Kingdom.
Posts: 3,074
Thanks: 1
Thanked 38 Times in 37 Posts
Default

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)
 
Old January 28th, 2008, 01:59 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: Hudson, MA, USA.
Posts: 839
Thanks: 0
Thanked 1 Time in 1 Post
Default

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
je.mason@comcast.net
 
Old January 29th, 2008, 04:31 AM
joefawcett's Avatar
Wrox Author
Points: 9,763, Level: 42
Points: 9,763, Level: 42 Points: 9,763, Level: 42 Points: 9,763, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Exeter, , United Kingdom.
Posts: 3,074
Thanks: 1
Thanked 38 Times in 37 Posts
Default

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)




Similar Threads
Thread Thread Starter Forum Replies Last Post
Query to retrieve the first 10 records inserted anup_daware Oracle ASP 0 August 6th, 2007 01:50 AM
Retrieve SOME records jaywhy13 Classic ASP Basics 1 January 31st, 2005 12:50 AM
Retrieve only updated or changed records pradeep_itguy SQL Server DTS 3 November 4th, 2004 10:55 PM
Qry to retrieve from 2 tables and display tsimsha Classic ASP Databases 3 October 29th, 2004 02:03 AM
data retrieve from three tables mateenmohd SQL Server 2000 2 October 7th, 2003 04:26 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.