Join Two tables and get the output data in same columns-CodePudding

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

CodePudding user response：

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

CodePudding user response：

See if this helps you:

SELECT name, id, max(city) city, is_match FROM (
SELECT TableA.name, TableA.id, TableA.city, IF (TableA.id = TableB.id, 'yes', 'no') is_match FROM TableA
LEFT JOIN TableB ON TableA.id = TableB.id
UNION
SELECT TableB.name, TableB.id, null, IF (TableA.id = TableB.id, 'yes', 'no') FROM TableB
LEFT JOIN TableA ON TableA.id = TableB.id
) TableC
GROUP BY name, id
ORDER BY 2

CodePudding user response：

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