CODEWITHSUNDEEP
sqlsql-server-2008sql-server-2005sql-server-2008-r2
user1599392
user1599392

Reputation: 175

Compare 2 tables in sql

I have two tables, A1 and A2. I want to compare these two tables. I tried inner join but it doesn't give the required result.

These are the data in these tables,

Table A1

No. Address 
1  abc
1  abc
1  def
1  def

Table A2

No. Address
1    def
1    abc
1    abc
1    def

These two tables can only be joined by using No. column. So if I use INNER JOIN it gives 16 rows. I don't want that, I want only 4 rows to be displayed. This should be the output:

No.   Address   eq
1     abc      #
1     abc      *
1     abc      #
1     abc      #

Last column is displayed if address in A1 is equal to A2

Upvotes: 1

Views: 3305

Answers (2)

user994860
user994860

Reputation: 13

try to use case

select case when tablea1.address=tablea2.address then '*' else '#' end as eq from tablea1 inner join tablea2 on tablea1.=tablea2.

hope it helps you.

Upvotes: 0

Arsen Mkrtchyan
Arsen Mkrtchyan

Reputation: 50752

Search for records that exist in A1 table but not in A2 table:

SELECT * FROM A1 WHERE NOT EXISTS (SELECT * FROM A2 WHERE A2.Id = A1.Id)

Upvotes: 2

Related Questions