CODEWITHSUNDEEP
sql-server
user2726975
user2726975

Reputation: 1353

Combining results of 2 tables with condition

I have two tables whose results I am trying to combine.

Create table dbo.streetaddr1(HomeID INT,Address varchar(200));

INSERT INTO dbo.streetaddr1 VALUES(1, '656 ave.');
INSERT INTO dbo.streetaddr1 VALUES(2,'B-6 ');
INSERT INTO dbo.streetaddr1 VALUES(3,'13 villa ');




Create table dbo.streetaddr2(HomeID INT,Address varchar(200));
   INSERT INTO dbo.streetaddr2 VALUES(1, '656 ave.');
   INSERT INTO dbo.streetaddr2 VALUES(2,'B-6 6th avene');
   INSERT INTO dbo.streetaddr2 VALUES(4,'25 Main street');
   INSERT INTO dbo.streetaddr2 VALUES(5,'135 Elm St ');

If a HomeID exists in dbo.streetaddr1, we pick Address from that even though it also exists in dbo.streetaddr2 we do not pick it. If a HomeID does not exists in dbo.streetaddr1 then we pick those addresses from dbo.streetaddr2

Expected output table is as below:

Create table dbo.outputtable(HomeID INT,Address varchar(200));
   INSERT INTO dbo.outputtable VALUES(1, '656 ave.');
   INSERT INTO dbo.outputtable VALUES(2,'B-6 ');
   INSERT INTO dbo.outputtable VALUES(3,'13 villa ');
   INSERT INTO dbo.outputtable VALUES(4,'25 Main street');
   INSERT INTO dbo.outputtable VALUES(5,'135 Elm St ');

How can I do that?

thanks Rs

Upvotes: 0

Views: 26

Answers (1)

Sateesh Pagolu
Sateesh Pagolu

Reputation: 9606

Try this

SELECT COALESCE(S1.HOMEID,S2.HOMEID) AS HOMEID,
       COALESCE(S1.ADDRESS,S2.ADDRESS) AS ADDRESS
FROM streetaddr1 S1 
        full join streetaddr2 s2 on s1.HomeId = s2.HomeId

Upvotes: 3

Related Questions