Including more than 1 Columns(Composite Keys) for joins

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

Transact-SQL (2000)

Including more than 1 Columns(Composite Keys) for joins

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2004-06-02 : 07:17:00

Jay writes "My Question is as Follows: I have 2 tables, table 1(say A for name sake) with Columns as CustId and BookID and a 2nd table (Say table B) with same set of Columns i.e. CustId and BookId now I would like to retrive all the rows that are not matching in the Both tables for based on CustID and BookId(take as a composite key).

I would like to know of any other solution for the above problem.

The solution that I have worked out is as follows..

Select A.CustId,A.BookId from A where CustId+BookId not in (Select A.CustId,A.BookId from A, B where A.CustId=B.CustID and A.BookId=B.BookId)"

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2004-06-02 : 07:32:40

This will bring back whats on a and not on b as well as whats on b and not on a.

select a.custid, a.bookid
from a
where not exists (select b.* from b where a.custid = b.custid and a.bookid = b.bookid)
union
select b.custid, b.bookid
from b
where not exists (select a.* from a where a.custid = b.custid and a.bookid = b.bookid)

Duane.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-06-02 : 08:58:24

i prefer this approach -- it returns all rows that are not exactly the same in both tables.

select min(tbl) as TableName, custID, bookID
from
(select 'Table A' as tbl, custID, bookID from a
union all
select 'Table B' as tbl, custID, bookID from b) tmp
group by custID, bookID
having count(*) = 1

just union them together and group by the key fields, and assign each part of the union a field to indicate which table that data is from. the min() in the select doesn't really matter -- because the count(*) is filter to always be 1, you can choose min() or max() to return the table name.

i have found this method to be very efficient and portable.

- Jeff

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2004-06-02 : 10:12:33

Nice One

Duane.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources