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

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.

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

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;

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

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.