Join Two tables and get the output data in same columns

Question

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

Stu · Accepted Answer · 2022-12-01 20:31:07Z

2

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

answered Dec 1, 2022 at 20:31

Stu

32.7k6 gold badges17 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

griv · Accepted Answer · 2022-12-01 21:15:15Z

1

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.

answered Dec 1, 2022 at 21:15

griv

2,2852 gold badges16 silver badges19 bronze badges

Comments

Cetin Basoz · Accepted Answer · 2022-12-01 20:04:55Z

0

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

answered Dec 1, 2022 at 20:04

Cetin Basoz

24k3 gold badges34 silver badges48 bronze badges

Collectives™ on Stack Overflow

Join Two tables and get the output data in same columns

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related