CODEWITHSUNDEEP
sqlpostgresql
Nawaz Sharif
Nawaz Sharif

Reputation: 59

how to combine multiple column of different table into one table

I have three tables a, b and c and need to arrange these table data as target table and all of these tables (a, b, c) are not in database they are fetched from from single table using queries as alias and need to arrange these tables into target table using query. How to do that

   table a     |      table b     |   table c     
               |                  |               
  id | a_vals  |     id | b_vals  |  id | c_vals  
  ------------ | --------------   | ------------- 
  1  |  123    |     1  |  123    |  1  |  123    
  2  |  124    |     2  |  142    |  2  |  142    
  3  |  234    |     4  |  234    |  5  |  234

target table

id | a_val| b_val| c_val
1  | 123  | 123  | 123
2  | 124  | 142  | 142
3  | 234  |  -   |  -
4  |  -   | 234  |  -
5  |  -   |      | 234

Upvotes: 0

Views: 162

Answers (5)

onedaywhen
onedaywhen

Reputation: 57053

This is probably better done in the 'front end' e.g. this is the kind of thing a reporting tool is designed for.

Avoiding nulls and outer joins (because they by definition produce nulls):

SELECT a_val, b_val, c_val
  FROM a 
       NATURAL JOIN b 
       NATURAL JOIN c
UNION
SELECT a_val, '-' AS  b_val, '-' AS c_val
  FROM a 
 WHERE id NOT IN ( SELECT id FROM b )
       AND id NOT IN ( SELECT id FROM c )
UNION
SELECT '-' AS a_val, b_val, '-' AS c_val
  FROM b
 WHERE id NOT IN ( SELECT id FROM a )
       AND id NOT IN ( SELECT id FROM c )
UNION
SELECT '-' AS a_val, '-' AS  b_val, c_val
  FROM c
 WHERE id NOT IN ( SELECT id FROM a )
       AND id NOT IN ( SELECT id FROM b );

Upvotes: 0

Linus Back
Linus Back

Reputation: 36

Since a, b and c share the same name for the column you want to join, you could use "USING" to avoid duplicate keys in the resulting table:

SELECT *
FROM a
FULL OUTER JOIN b USING (id)
  FULL OUTER JOIN c USING (id);

Alternativly, since a, b and c's value column all have distinct names you could use NATURAL JOIN:

SELECT *
FROM a
NATURAL FULL OUTER JOIN b
NATURAL FULL OUTER JOIN c;

Be careful not to accidentally rename any of the other columns tho, since natural join removes any duplicate columns. You can also omit the "OUTER" keyword if you like, but i would leave it for clarity, (since LEFT, RIGHT, and FULL imply an outer join). See https://www.postgresql.org/docs/10/static/queries-table-expressions.html for details

Upvotes: 2

Pankaj Kumar
Pankaj Kumar

Reputation: 570

Please try this: 

 select aa.id, a_val, b_val, c_val from 
 (select distinct id as id from table_a
 union
 select distinct id as id from table_b
 union
 select distinct id as id from table_c)aa
 left join (select id, a_val from table_a)bb on aa.id = bb.id
 left join (select id, b_val from table_b)cc on aa.id = cc.id
 left join (select id, c_val from table_c)dd on aa.id = dd.id order by aa.id;

Upvotes: 2

Praveen S
Praveen S

Reputation: 395

Try this code 

SELECT 
CASE
    WHEN t1.id IS not null THEN t1.id
    WHEN t2.id IS not null THEN t2.id  
    ELSE t3.id
  END 
  AS id,
t1.a_vals AS a_val, 
t2.b_vals as b_val, 
t3.c_vals as c_val
FROM a t1  FULL OUTER JOIN b t2 ON t1.id=t2.id FULL OUTER JOIN c t3 ON 
CASE
    WHEN t1.id IS not null THEN t1.id
    ELSE t2.id
  END = t3.id

OR

SELECT  COALESCE(t1.id, t2.id, t3.id) as id ,
            t1.a_vals AS a_val, 
            t2.b_vals as b_val, 
            t3.c_vals as c_val
    FROM a t1 FULL OUTER JOIN b t2 ON t1.id=t2.id 
    FULL OUTER JOIN c t3 ON COALESCE(t1.id, t2.id) = t3.id

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270351

You are looking for the ANSI-standard FULL OUTER JOIN:

select coalesce(a.id, b.id, c.id) as id, a.val, b.val, c.val
from a full join
     b
     on a.id = b.id full join
     c
     on c.id = coalesce(a.id, b.id);

You can also implement this with union all/group by:

select id, max(a_val) as a_val, max(b_val) as b_val, max(c_val) as c_val
from ((select id, val as a_val, null as b_val, null as c_val
       from a
      ) union all
      (select id, null as a_val, val as b_val, null as c_val
       from b
      ) union all
      (select id, null as a_val, null as b_val, val as c_val
       from c
      )
     ) abc
group by id;

Upvotes: 0

Related Questions