Finding combinations

Question

Finding combinations

Renis Cerga.

SSC Enthusiast

Points: 127
More actions
September 27, 2009 at 6:35 am

#156261

Hello,
Can somebody help me with this scenario:
I have 2 tables:
First one (Lets say TableA) has only one column (MainColumn). Second one (TableB) has 2 columns (FirstColumn, SecondColumn).
TableA
MC
1
2
3
4
5
TableB
C1 C2
1 7
4 6
2 5
I want to check if at least 2 elements from TableA are present in the same record in TableB. In above scenario there is only 1 recurrence, the last one with values 2 and 5.
Can someone help me?
Thank you!

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

roger young-372270 SSC Veteran Points: 232 More actions · Answer 1

When you approach this problem you need to dissect what you are trying to accomplish. Since both columns of your "tableb" need to be joined to the "tablea" you can see where you will need two joins However joining all three tables in your main statement will not work because you are looking for combinations and a three way join will require both values to be the same in tableb (2,2).

You can just break this apart into two steps. First join the tablea to tableb on the first column as a subquery, then join your subquery back to table a using the second column.

Declare @tablea table

(

id int

)

Insert into @tablea (id)

Select 1 union all

Select 2 union all

Select 3 union all

Select 4 union all

Select 5

Declare @tableb table

(

id1 int

,id2 int

)

Insert into @tableb (id1,id2)

Select 1,7 union all

Select 4,6 union all

Select 2,5

Select id1match.id1,id1match.id2 from

(Select id1,id2 from @tableb

join @tablea on id = id1) as id1match

join @tablea ta on id1match.id2 = ta.id

Mark Cowne One Orange Chip Points: 26967 More actions · Answer 2

SELECT B.C1,B.C2

FROM TableB B

INNER JOIN TableA A1 ON A1.MC=B.C1

INNER JOIN TableA A2 ON A2.MC=B.C2

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

Renis Cerga. SSC Enthusiast Points: 127 More actions · Answer 3

Thank you so much Mark! Very elegant solution... I was also thinking for double join, but was falling in the same trap as Roger, thinking that it will not work, because both sides have to be equal in the same record!

Thx again!