CODEWITHSUNDEEP
sqlsql-serverjoin
Keshav
Keshav

Reputation: 69

Join Two tables and get the output data in same columns

I have two tables A and B, Table A have 3 cols and table B have 2 cols.

Table A data:

name id city
xyz 1 ab
xyz2 2 ab1
xyz3 3 ab2

Table B data:

name id
xyz3 3
abc2 4

Output I want:

name id city match
xyz 1 ab no
xyz2 2 ab1 no
xyz3 3 ab2 yes
abc2 4 NULL no

I have tried this but it is giving op in different column:

select *
from TableA a
full outer join TableB b
on a.id= b.id

Output I'm getting

name id city name id
xyz 1 ab null null
xyz2 2 ab1 null null
xyz3 3 ab2 xyz3 3
Null null null abc2 4

Output I want:

name id city match
xyz 1 ab no
xyz2 2 ab1 no
xyz3 3 ab2 yes
abc2 4 NULL no

Upvotes: 2

Views: 929

Answers (3)

griv
griv

Reputation: 2245

You can use a UNION between TableA and TableB, a CASE statement to check if an id in TableA is also inTableB (for your match column), and a NOT EXISTS subquery to TableA in TableB's WHERE clause to only return rows that exist in TableB.

SELECT *, 
  CASE WHEN id IN (SELECT DISTINCT id FROM TableB) THEN 'yes' ELSE 'no' END AS match
FROM TableA a
   UNION
SELECT *, NULL AS city, 'no' AS match
FROM TableB b 
  WHERE NOT EXISTS (SELECT 1 FROM TableA a2 WHERE a2.id = b.id)
ORDER BY id ASC

Result:

| name | id | city | match  |
|------|----|------|--------|
| xyz  | 1  | ab   | no     |
| xyz2 | 2  | ab1  | no     |
| xyz3 | 3  | ab2  | yes    |
| abc2 | 4  | null | no     |

Fiddle here.

Upvotes: 1

Stu
Stu

Reputation: 32614

The following is all I think you need to do - union the two tables and then aggregate, counting rows to identify matches with duplicates from both tables:

with c as (
    select name, id, city
    from a union all
    select name, id, null
    from b
)
select name, id, Max(city) City,
  case when Count(*)> 1 then 'yes' else 'no' end Match
from c
group by name, id;

See demo fiddle

Upvotes: 2

Cetin Basoz
Cetin Basoz

Reputation: 23837

select * from (
select a.name, a.id, a.city, 
  case when b.id is not null then 'Yes' else 'No' end as [Match]
from tableA a
  left join tableB b  on b.name = a.name and b.id = a. id
union 
select b.name, b.id, a.city,
  case when a.id is not null then 'Yes' else 'No' end as [Match]
from tableB b left join tableA a on b.name = a.name and b.id = a. id) tmp
  order by id  ;

Here is DBFiddle demo

Upvotes: 0

Related Questions