Clever Geek Handbook

SQL Multiple inner joins between two tables

I know this is a pretty simple problem, but it has been years since I looked into SQL. I have two tables containing information.

Business partners table

Partner
1
2
3

and the Business Relationships table

RelationshipNum Partner1 Partner2 Relationshiptype
000001          1        2        OCDL

My query should take values ​​for Partner1 and Partner2 and look in the Partners field in business partners. If both values ​​are present in the same Business Relationships record, the entire row should be returned to Business Relationships. If only one or none is present, we can ignore it.

This is what I have so far, but does not return any values, I have tested the dataset in excel (too large to be a viable option) and there are definite matches.

SELECT  [Business Relationships].*
FROM [Business Relationships] 
INNER JOIN [Business Partners] ON ([Business Partners].Partner = [Business Relationships].Partner1) 
                               AND ([Business Partners].Partner = [Business Relationships].Partner2);
+3


source to share


2 answers


Try with two INNER JOIN

like below



SELECT  BR.*
FROM ([Business Relationships] BR
INNER JOIN [Business Partners] BP1 ON BP1.[Partner] = BR.[Partner1])
INNER JOIN [Business Partners] BP2 ON BP2.[Partner] = BR.[Partner2]
+4


source


Use double EXISTS

to find the returned strings:

SELECT  BR.*
FROM [Business Relationships] BR
WHERE EXISTS (select 1 from [Business Partners] where Partner = BR.Partner1)
  AND EXISTS (select 1 from [Business Partners] where Partner = BR.Partner2)


Will never return duplicates! (What solutions can a JOIN do, depending on the data.)

+1


source






More articles:


All Articles