CODEWITHSUNDEEP
sqlsql-server-2008
Muflix
Muflix

Reputation: 6778

Full outer join on two unique column combinations

i have interesting issue and im not sure how to handle it.

I have 2 tables and i need to join same data using identificators and add data which are missing at left table or right table. Its seems like FULL OUTER JOIN but, the problem is that i have 2 combinations of keys.

For example T1_Identificator1 + T1_Identificator3 = T2_Identificator1 + T2_Identificator3 = Same record T1_Identificator2 + T1_Identificator3 = T2_Identificator2 + T2_Identificator3 = Same record

I though about some Subselect on each record but it seems to me too much complicated and also with considerable performance lost

Second idea, which im trying just now is with EXCEPT and INTERSECT

enter image description here

Have you some idea how to handle this ? I prepared DB Schema http://sqlfiddle.com/#!6/5de09

Thank you

Upvotes: 0

Views: 248

Answers (1)

Giorgos Betsos
Giorgos Betsos

Reputation: 72175

Just do a FULL JOIN using the condition you have described:

SELECT *
FROM T1
FULL OUTER JOIN T2 
ON (T1_Identificator1 + T1_Identificator3 = T2_Identificator1 + T2_Identificator3 )
   OR (T1_Identificator2 + T1_Identificator3 = T2_Identificator2 + T2_Identificator3)

Check this SQL Fiddle Demo. It produces exactly the same output as the wanted one cited in the OP.

Upvotes: 3

Related Questions