How to Merge the Columns from Two Tables into One Output

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

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.

Merge multiple columns from different tables into one

You can use join and union

select date, Description from tblEvents
union all
select DOB,'Birthday '+Name from tblPeople a inner join tblEvents
on month(date)=month(DOB)

Combine three columns from different tables into one row

Use a cross-join like this:

create table table1 (field1 char(10));
create table table2 (field2 char(10));
create table table3 (field3 char(10));

insert into table1 values('value1');
insert into table2 values('value2');
insert into table3 values('value3');

select *
  from table1
  cross join table2
  cross join table3;

Result:

field1     field2     field3
---------- ---------- ----------
value1     value2     value3

A cross join joins all the rows on the left with all the rows on the right. You will end up with a product of rows (table1 rows x table2 rows x table3 rows). Since each table only has one row, you will get (1 x 1 x 1) = 1 row.

---

Related Topics