How can I merge the columns from two tables into one output

Question

I have two tables with similar information  Let s call them items a and items b  They should be one  but they are coming from different sources  so they aren t  When I full-join the two table  some rows end up with data from either one or both tables  One of the columns in both tables is category id  I would like to cross the combined table with the categories table using category id  However  I have two category id columns now  one from items a and one from items b   Is there a way to merge the two columns into one   I hope this isn t too confusing of a question

User · Answer

SELECT col1    col2 FROM    SELECT rownum X col table1 FROM table1  T1 INNER JOIN    SELECT rownum Y  col table2 FROM table2  T2 ON T1 X T2 Y

User · Answer

Specifying the columns on your query should do the trick   select a col1  b col2  a col3  b col4  a category id  from items a a  items b b  where a category id   b category id   should do the trick with regards to picking the columns you want   To get around the fact that some data is only in items a and some data is only in items b  you would be able to do   select    coalesce a col1  b col1  as col1     coalesce a col2  b col2  as col2    coalesce a col3  b col3  as col3    a category id from items a a  items b b where a category id   b category id   The coalesce function will return the first non-null value  so for each row if col1 is non null  it ll use that  otherwise it ll get the value from col2  etc

User · Answer

When your are three tables or more  just add union and left outer join   select a col1  b col2  a col3  b col4  a category id  from        select category id from a     union     select category id from b   as c left outer join a on a category id   c category id left outer join b on b category id   c category id

User · Answer

I had a similar problem with a small difference  some a category id are not in b and some b category id are not in a  To solve this problem just adapt the excelent answer from beny23 to     select a col1  b col2  a col3  b col4  a category id  from items a a LEFT OUTER JOIN items b b  on a category id   b category id   Hope this helps someone  Regards

User · Answer

I guess that what you want to do is an UNION of both tables   If both tables have the same columns then you can just do   SELECT category id  col1  col2  col3   FROM items a UNION  SELECT category id  col1  col2  col3    FROM items b   Else  you might have to do something like   SELECT category id  col1  col2  col3   FROM items a  UNION  SELECT category id  col 1 as col1  col 2 as col2  col 3 as col3   FROM items b

[sql] How can I merge the columns from two tables into one output?

Examples related to sql

Examples related to postgresql