CODEWITHSUNDEEP
mysql
Jay85
Jay85

Reputation: 136

Fetch same column from multiple tables and values of other columns where exist

I have below table structure.

Table A

id    | name
1       name of product1
2       name of product2
3       name of product3

Second Table as:

Table B

id    | name
1       diff name of product1
3       diff name of product3
4       name of product4
5       name of product5

Third Table as:

Table C

id    | name
2       difffff name of product2
3       difffff name of product3
6       name of product6
7       name of product7

How can I join them, to get output as below:

id    | name
1       name from either Table A or B
2       name from either Table A or C
3       name from either Table A or B or C
4       name from Table B
5       name from Table B
6       name from Table C
7       name from Table C

I know I can get list of IDs by UNION, but when I add name column, then IDs get duplicated.

Any suggestion?

Upvotes: 0

Views: 38

Answers (2)

ScaisEdge
ScaisEdge

Reputation: 133360

You could use union for get all the name then left join the names with the tables (A,B,C) check for not null values and last use a (fake) aggregation fnction and group by for reduce the rows 

select  t.name
   , MAX(case when A.name IS NOT NULL THEN 'from TABLE A' END) fromA
   , MAX(case when B.name IS NOT NULL THEN 'from TABLE B' END) fromB
   , MAX(case when C.name IS NOT NULL THEN 'from TABLE C' END) fromC
from (
  select  name
  from tableA
  union 
  select  name
  from tableB 
  select  name
  from tableC
) T
left join tableA A ON A.name = T.name 
left join tableB B ON B.name = T.name 
left join tableC C ON C.name = T.name 
group by t.name

Upvotes: 0

forpas
forpas

Reputation: 164089

If you don't care about which table the name comes from use UNION ALL and group by id:

select
  id, max(name) name
from (
  select id, name from tablea
  union all
  select id, name from tableb
  union all
  select id, name from tablec
) t
group by id
order by id

See the demo.
Results:

| id  | name             |
| --- | ---------------- |
| 1   | name of product1 |
| 2   | name of product2 |
| 3   | name of product3 |
| 4   | name of product4 |
| 5   | name of product5 |
| 6   | name of product6 |
| 7   | name of product7 |

Upvotes: 1

Related Questions