Postgresql – Combine multiples table to a new one

postgresqlquery

I apologize, I am not sure how to word this question. It probably has been answered somewhere, but I couldn't find it if so.

I have three tables that I want to combine in one using a common id column, with empty rows here and there.

table1

 id | col1 
----|------ 
  1 | 23.4 
  3 | 13.4 
  4 |  7.6

table2

 id | col2 
----|------ 
  1 | 12.2 
  2 | 17.1

table3

 id | col3 
----|------ 
  1 | 22.2 
  2 | 14.1 
  5 | 10.8

I want to create a fourth table table4:

 id | col1 | col2 | col3 
----|------|------|------ 
  1 | 23.4 | 12.2 | 22.2 
  2 | NULL | 17.2 | 14.1 
  3 | 13.4 | NULL | NULL 
  4 | 7.6  | NULL | NULL 
  5 | NULL | NULL | 10.8

I'm stumped as to how to approach this problem honestly.

Best Answer

I think you are looking for a FULL OUTER JOIN:

SELECT COALESCE(a.id,b.id,c.id), COALESCE(a.col1,'NULL'), COALESCE(b.col2,'NULL'), COALESCE(c.col3,'NULL')
  FROM table1 a
  FULL OUTER JOIN table2 b ON a.id=b.id
  FULL OUTER JOIN table3 c ON b.id=c.id;

or, if you want blanks instead of strings:

SELECT COALESCE(a.id,b.id,c.id), a.col1, b.col2, c.col3
  FROM table1 a
  FULL OUTER JOIN table2 b ON a.id=b.id
  FULL OUTER JOIN table3 c ON b.id=c.id;

Related Solutions

Sql-server – Query to normalize table/combine row text

This should work, I will clean it up later so its more efficient.

DECLARE @Old TABLE ( 
  id         INT, 
  rank       INT, 
  linenumber INT, 
  sometext   VARCHAR(1000)) 
DECLARE @New TABLE ( 
  id           INT, 
  rank         INT, 
  combinedtext VARCHAR(1000)) 


;WITH combinedresults(ctid, id, rank, linenumber, combinedtext) 
     AS (SELECT 0, 
                id, 
                rank, 
                linenumber, 
                CAST (sometext AS VARCHAR(8000)) 
         FROM   @Old o 
         WHERE  NOT EXISTS (SELECT TOP 1 1 
                            FROM   @Old 
                            WHERE  id = o.id 
                                   AND rank = o.rank 
                                   AND linenumber < o.linenumber) 
         UNION ALL 
         SELECT ctid + 1, 
                o.id, 
                o.rank, 
                o.linenumber, 
                ct.combinedtext + o.sometext 
         FROM   @Old o 
                INNER JOIN combinedresults ct 
                  ON ct.id = o.id 
                     AND ct.rank = o.rank 
         WHERE  o.linenumber > ct.linenumber) 

UPDATE n 
SET    combinedtext = ct.combinedtext 
FROM   @New n 
       INNER JOIN (SELECT n.id, 
                          n.rank, 
                          MAX(o.rank) orank 
                   FROM   @new n 
                          INNER JOIN @Old o 
                            ON n.id = o.id 
                               AND o.rank <= n.rank 
                   GROUP  BY n.id, 
                             n.rank) r 
         ON n.id = r.id 
            AND n.rank = r.rank 
       INNER JOIN (SELECT id, 
                          ct.rank, 
                          MAX(ctid) ctid 
                   FROM   combinedresults ct 
                   GROUP  BY ct.id, 
                             ct.rank) r2 
         ON r2.id = r.id 
            AND r2.rank = r.orank 
       INNER JOIN combinedresults ct 
         ON r.id = ct.id 
            AND ct.rank = r.orank 
            AND ct.ctid = r2.ctid 

SELECT * 
FROM   @New

Combine column from multiple rows into one row in SQLITE

You can use the GROUP_CONCAT() function:

SELECT a.Name               AS ArticleName
     , GROUP_CONCAT(t.Name) AS TagList
FROM Articles AS a
  LEFT JOIN ArticlesTags AS at
    ON at.ArticleId = a.Id
  LEFT JOIN Tags AS t
    ON t.Id = at.TagId
GROUP BY a.Id, a.Name ;