Question on joins

I have 4 tbls , TBL1, TBLB, TBLC and TBLD... All these 4 tbls have an accoutnnumber field, which should be unique between all these 4 btls.

How, do I check to see if any acctnumbers are present in more than 1 of these 4 tbls?

I used an inner join , but that ONLY Looks for accts, that are repeated in ALL These 4 tbls. Heres the qry I used:

What I need to see is if a acct is repeated Between ANY Of these tbls, it should return that acct as a duplicate.

Pl advise how I can do that:

select * FROM TblA a
inner join TblB cr
on cr.acctno=a.acctno
inner JOIN TblC fc
on fc.acctno=a.acctno
inner join TblD r
on r.acct#=a.acctno

Code Snippet

select AcctNo,count(*)

from (

select AcctNo, 'A' as Table from TableA

union all

select AcctNo, 'B' as Table from TableB

union all

select AcctNo, 'C' as Table from TableC

union all

select AcctNo, 'D' as Table from TableD

) as [SubQuery]

group by AcctNo

having count(*) > 1

|||

May be this..

Code Snippet

Create Table #tab1 (

[id] int

);

Insert Into #tab1 Values('1');

Insert Into #tab1 Values('3');

Insert Into #tab1 Values('4');

Create Table #tab2 (

[id] int

);

Insert Into #tab2 Values('1');

Insert Into #tab2 Values('3');

Create Table #tab3 (

[id] int

);

Insert Into #tab3 Values('4');

Insert Into #tab3 Values('7');

Insert Into #tab3 Values('8');

Insert Into #tab3 Values('9');

Create Table #tab4 (

[id] int

);

Insert Into #tab4 Values('1');

Insert Into #tab4 Values('5');

Insert Into #tab4 Values('9');

Code Snippet

Select

Id,

Case When DuplicateAt & 1 = 1 Then 'Y' Else '' End [With tab1],

Case When DuplicateAt & 2 = 2 Then 'Y' Else '' End [With tab2],

Case When DuplicateAt & 4 = 4 Then 'Y' Else '' End [With tab3],

Case When DuplicateAt & 8 = 8 Then 'Y' Else '' End [With tab4]

From

(

Select

Id,

Sum(TabId) DuplicateAt

from

(

Select Id,1 TabId from #tab1

union all

Select Id,2 from #tab2

union all

Select Id,4 from #tab3

union all

Select Id,8 from #tab4

) as data

group By Id

having Count(Id) >1

) as data

|||

Hi,

Whenever I am faced with little problems like these, I tend to go back to absolute basics. If you think of each one of your tables as a data set (not a table).

Even better, imagine your have 4 quarter decks of playing cards (13 cards each). To answer the question of which playing cards are duplicated in any of the 4 decks. You would have to mix the cards together and group them and then look for duplicates. If you can think of a better way to do this in real life then let me know!

Luckily, the SQL Server engine does most of the work for us. That's the beauty of set-based logic. So to implement a solution we know we need to combine our 4 sets of playing cards. T-SQL gives us the UNION ALL option to combine similar data sets.

set A

UNION ALL

set B

UNION ALL

set C

UNION ALL

set D

To locate the duplicates in this example, we would group by the card number. If we find more than one then we know we have a duplicate.

Manivannan query follows a similar logic, only his code eliminates unique rows by using the having clause to filter on the grouped data. He also introduces a CASE statement for presenting the result in a more meaningful way but his logic is essentially the same. The bitwise operator used in the case statement is useful here to mark each of your tables. This is useful to answer the question of which table contained the duplicate

Hope this helps.