CODEWITHSUNDEEP
sqljoingroup-by
AKIWEB
AKIWEB

Reputation: 19632

Merge the Count from two table in One

This is below data in Table2

ID2        |    Count2
-----------+-----------
1345653         5
534140349       5
682527813       4
687612723       3
704318001       5

This is my below data in Table1

ID1        |    Count1
-----------+-----------
1345653         4
704318001       4

If you see the above table this 1345653 in Table2 has 5 count but in Table1 it has 4 count, In the same way this ID 704318001 in Table2 has 5 count but in Table1 it has 4 count. So I need to show like this in the Output.

ID              Count2   Count1
-----------------------------------
1345653         5        4
534140349       5        0
682527813       4        0
687612723       3        0
704318001       5        4

Is this possible in sql? If Yes, how can I achieve that?

Upvotes: 1

Views: 88

Answers (4)

Irfarino
Irfarino

Reputation: 550

Here you go Take a look at the link below http://sqlfiddle.com/#!3/69056/1

Upvotes: 1

dbenham
dbenham

Reputation: 130919

SQL 101

select id2, count2, coalesce(count1, 0) as count1
  from table2
  left outer join table1
    on id1=id2

Upvotes: 1

Sudhakar B
Sudhakar B

Reputation: 1573

try this

Select T1.ID,T1.Count1 As Count2,(Case When T2.ID1 IS NULL THEN 0 ELSE T2.Count1) 
As Count1 From 
Tabl1 AS T1 LEFT OUTER JOIN Table2 AS T2 ON 
T1.ID1=T2.ID1

Upvotes: 0

iruvar
iruvar

Reputation: 23394

select isnull(ID1, ID2) ID,  isnull(Table2.count2, 0) count2, isnull(Table1.count1, 0) count1
 from Table2 full outer join Table1 
 on Table2.ID2 = Table1.ID1

Upvotes: 0

Related Questions