I have a table
Table 1 : It the final table contains all data . ID and IDS are composite key
ID IDS name 1 PL35 Bumper 151111 PL35 Bumper 151111 PL36 Bumper 1516 PL35 TUMI 151511 PL36 Limo 151521 PL35 Superb 151521 PL36 Superb
table 2 : Its a pre final table which will upcoming data with incomplete information
ID IDS name 15100 PL35 NULL 1516 PL35 NULL 151521 PL36 NULL 151511 PL36 NULL
EXPECTED RESULT : Some IDs (ID+IDS) are in Table 1 and some are in Table 2 . I need to compare the data of table 1 and table 2 . The rule is Keep the common data with Table 1 information Keep the new row with (id+IDS) which is in table 2 but not in table 1
for eg;
- (15100 + PL35) is in table 2 but not in table 1 then it will remain
- (1516 + PL35) is common in both then the row from table 1 will remain.
- ( 151511 + PL36) is also common hence will remain .
- The data (ID +IDs) which is not in table 2 but in table 1 is not needed.
ID IDS name 15100 PL35 NULL 1516 PL35 TUMI 151511 PL36 Superb
SO far I am only think about this
select * from table1 t1 inner join table2 t2 on t1.id=t2.id
Advertisement
Answer
Use INTERSECT, named after the equivalent set operation.
SELECT ID, IDS FROM table1 INTERSECT SELECT ID, IDS FROM table2
EDIT In answer to your question, you can wrap this in a CTE and then join back onto table1.
WITH common AS ( SELECT ID, IDS FROM table1 INTERSECT SELECT ID, IDS FROM table2 ) SELECT c.*, t.Name FROM common c INNER JOIN table1 t ON c.ID = t.ID AND c.IDS = t.IDS