Results of two tables - 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 2000 Forums

SQL Server Development (2000)

Results of two tables

Author

Topic

olily
Starting Member

37 Posts

Posted - 2002-05-20 : 19:25:48

I have two different tables where they are linked by CustID.
Table1 - CustID, CompanyCode, Amount
Table2 - CustID, Balance

In Table1, same CustID+CompanyCode is primary key whereas in Table2 CustID is primary key. I would like to select all the CustID that exist in Table1 and Table2. For example:

Table1
A, Company1, 100
B, Company2, 120
C, Company1, 130
C, Company2, 140
D, Company2, 150

Table2
A, 1000
B, 2000
D, 3000
E, 4000

So, the result for my query is A, B, C, D, E. Please advice.

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2002-05-20 : 20:58:03

SELECT CustID FROM Table1
UNION
SELECT CustID FROM TAble2

joldham
Wiseass Yak Posting Master

300 Posts

Posted - 2002-05-20 : 21:36:31

AjarnMark's query would return all CustId's, but chances are you will have duplicates. If this is what you want, use his query. If you want the results without duplicates, use the following:

Select Distinct a.CustID
FROM (SELECT CustID FROM Table1
UNION
SELECT CustID FROM TAble2) a

Jeremy

simondeutsch
Aged Yak Warrior

547 Posts

Posted - 2002-05-20 : 22:12:39

UNION eliminates duplicates, unless you specify UNION ALL.

Sarah Berger MCSD

olily
Starting Member

37 Posts

Posted - 2002-05-21 : 00:50:02

Thanks!

joldham
Wiseass Yak Posting Master

300 Posts

Posted - 2002-05-21 : 08:07:35

Thanks Sarah,

I stand corrected and learn something new everyday!

Jeremy