joining 2 tables based on multiple records???

[BradEdwards]

I have a table with Acct #'s and CIF #'s. Some Accts have multiple customers associated with them (ie. Husband and wife). See Example below.

example:
Acct# CIF#
123 CIF01
123 CIF02
345 CIF01
567 CIF03
789 CIF02
789 CIF03

We have an imaging system where we scan in Signature Cards and index them using the CIF #'s (ie scan in signature card and add CIF01 and CIF02 as keywords). I need to write a query to compare ALL the cif #'s on the Signature Cards and add the corresponding Acct #'s. The trick is I can only add an acct # that satisfy's all the CIF #'s on that document. In other words if I have a Sig Card with CIF01 and CIF02 on it even though CIF01 belongs on both Accts 123 and 345 (see above), it would only put Acct 123 on the sig card because all the CIF #'s must match. I can do this with a bunch of joins but the problem is I would have to dynamically build the joins because there can be anywhere from 1 to 10 (or more) people on a sig card. I was hoping there was an easier way to do this. Originally I was creating a temporary table with the following information in it and then doing a bunch of joins with the acct# table.

Temp Table
itemnum CIF#
1 CIF01
1 CIF02
2 CIF01
3 CIF03
4 CIF02
4 CIF03

So Itemnum 1 would have both CIF01 and CIF02 as keywords and acct # 123 would be added as an Acct# keyword (index) and so on. Hopefully all this makes sense. it's a little hard to explain in writing. Any suggestions would be appreciated. Thanks.

 

[pabowen]

Brad, I am not sure I have any clue what you are asking. Can you reexplain? Show us what the table looks like, and what you want the results to look like, and then any business rules around that.

I know you already did most of that, but I can't figure out what you are trying to get at?

Patrick

 

[BradEdwards]

Okay Patrick I'll try this again...but I knew this would be hard to explain...

I'm scanning signature cards for bank customers into an imaging system. The Sig Cards have 2 keywords (indexes) on them...Account # and CIF #. The CIF # is a unique number used to identify a customer. When we scan in the sig card we will index it by putting on the CIF #'s of the customers. I then want to run a query based on a host file which lists the Account #'s and CIF#'s belonging to those Accounts and add the corresponding Account #'s to the Sig Cards. Okay so here's some examples:

I have 2 sig cards in the system.
Sig Card 1 has 1 CIF #: CIF001 - Donald Duck
Sig Card 2 has 2 CIF #: CIF001 - Donald Duck, CIF002 Daisy Duck

I'll create a table that lists this information as such. Each document will have a unique identifier called itemnum.

Itemnum - CIF#
1 - CIF001
2 - CIF001
2 - CIF002
3 - CIF002
3 - CIF003
3 - CIF004

I then have a table with Acct#'s and corresponding CIF#'s as mentioned above.

Acct# - CIF#
123 - CIF001
789 - CIF001
789 - CIF002
555 - CIF002
555 - CIF003
555 - CIF004

So far we have Donald Duck who has 2 Accounts (123,789) and Daisy Duck has 1 Account (789) whom she shares with Donald.

I need to be able to add the appropriate Acct #'s to each of the sig cards, but my problem is the I can only add an Acct # to the sig card if ALL CIF #'s match. So for itemnum 1 I can only add Acct# 123 (even though he has an account 789) but because CIF002 is not on acct# 123 it doesn't get added. So here is what the finished table would look like:

Itemnum - Acct#
1 - 123
2 - 789
3 - 555

Now I can do all of this through a cursor which will probably be very slow or I can do it through a sql stmt that I would have to build dynamically because I have no idea how many joins I would need until I count the # of CIF #'s on the sig card and it can vary. And if I have a sig card with 10 signers on it it's going to be a huge query. Hopefully that makes more sense. If it doesn't then I guess I'll have to either go the cursor route or the long query route. Thanks for you help.

 

[vongrunt]

Awright... here is sample data:

create table Acct_CIF
(	Acct# int,
	CIF# char(5)
	primary key (Acct#, CIF#)
)

insert into Acct_CIF values (123, 'CIF01')
insert into Acct_CIF values (123, 'CIF02')
insert into Acct_CIF values (345, 'CIF01')
insert into Acct_CIF values (567, 'CIF03')
insert into Acct_CIF values (789, 'CIF02')
insert into Acct_CIF values (789, 'CIF03')

create table SignatureCards
(	itemNum int,
	CIF# char(5)
)

-- matches Acct# 123 (2 vs 2). Does NOT match Acct# 345 (2 vs 1)
insert into SignatureCards values (1, 'CIF01')
insert into SignatureCards values (1, 'CIF02')

-- does NOT match Acct# 789 (3 vs 2)
insert into SignatureCards values (2, 'CIF01')
insert into SignatureCards values (2, 'CIF02')
insert into SignatureCards values (2, 'CIF03')

-- matches Acct 567# (1 vs 1). Does NOT match Acct# 789 (1 vs 2)
insert into SignatureCards values (3, 'CIF03')

Are expected results described in comments OK or not?

------
[small]<this is sig>
select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')
</this is sig>[/small]

 

[BradEdwards]

vongrunt,

The 2 tables that you created are what I already have. I need another table that tells me what Acct#'s I need to add to the itemnum's (sig cards). So in my first example the final table would look like this

Create Table AddAccts
( itemnum int
Acct# int
)

--matches Acct# 123 (2 vs 2). Does NOT match Acct# 345 (2 vs 1)
insert into AddAccts values(1, 123)
--matches Acct# 345 (1 vs 1). Does NOT match Acct# 123 (2 vs 1)
insert into AddAccts values(2, 345)
--matches Acct# 567 (1 vs 1). Does NOT match Acct# 789 (2 vs 1)
insert into AddAccts values(3, 567)
--matches Acct# 789(2 vs 2). Does NOT match Acct# 123 or 567
insert into AddAccts values(4, 789)

Thanks.

 

[vongrunt]

> The 2 tables that you created are what I already have.

I intentionally made another example because of 1 vs 2 case (not present in your sample).

So if itemNum has N CIFs, any related Acct# must have EXACTLY N CIFs too? No more, no less?



------
[small]<this is sig>
select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')
</this is sig>[/small]

 

[BradEdwards]

oic.

> So if itemNum has N CIFs, any related Acct# must have EXACTLY N CIFs too? No more, no less?

That is correct.

 

[vongrunt]

Here we go:

select Y.itemNum, Y.Acct#
from
(	select itemNum, count(*) as cnt
	from SignatureCards
	group by itemNum
) X
inner join
(	select SC.itemNum, AC.Acct#, count(*) as cnt
	from SignatureCards SC
	inner join Acct_CIF AC on SC.CIF# = AC.CIF#
	group by SC.itemNum, AC.Acct#
) Y
on X.itemNum = Y.itemNum
inner join
(	select Acct#, count(*) as cnt
	from Acct_CIF
	group by Acct#
) Z
on Y.Acct# = Z.Acct#
where X.cnt = Y.cnt
	and Y.cnt = Z.cnt

General idea: make 3 derived tables (X, Y and Z) with unique attributes and counts - acting like many-to-many (X against Z) with junction table (Y). Join 'em, compare counts and voila.

------
[small]<this is sig>
select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')
</this is sig>[/small]

 

[BradEdwards]

Very interesting. I'll give that a shot and let you know how that works. Thanks a bunch.

 

[BradEdwards]

Very impressive vongrunt. I checked it against some test data and it works perfectly. I'm still trying to figure out why it works, but very impressive. Thanks a lot.