Hi. I've got 2 select statements, returning data like this:
Select 1 col_a col_b
Select 2 col_a col_c
If I do union, I get something like col_a col_b
And rows joined. What i need is getting it like this: col_a col_b col_c
Joined on data in col_a.
+5
A:
Use JOIN to join the subqueries and use ON to say where the rows from each subquery must match:
SELECT T1.col_a, T1.col_b, T2.col_c
FROM (SELECT col_a, col_b, ...etc...) AS T1
JOIN (SELECT col_a, col_c, ...etc...) AS T2
ON T1.col_a = T2.col_a
If there are some values of col_a that are in T1 but not in T2, you can use a LEFT OUTER JOIN instead.
Mark Byers
2010-02-23 11:32:41
The problem is, i have select statements, not tables.
Vance
2010-02-23 11:34:01
@Vance: Then use a derived table and an alias, as shown in my updated answer.
Mark Byers
2010-02-23 11:35:51
Thanx, that did help.
Vance
2010-02-23 11:41:27
if your selects are long and your SQL implements it, you can use the WITH statement. With Select1 AS (select ..), Select2 AS (select ...) SELECT COALESCE(T1.col_a, T2.col_a) AS col_a, T1.col_b, T2.col_c FROM Select1 T1 FULL JOIN Select2 T2 ON T1.col_a = T2.col_a
sergiom
2010-02-23 11:44:36
+1
A:
SELECT table1.col_a, table1.col_b, table2.col_c
FROM table1
INNER JOIN table2 ON table1.col_a = table2.col_a
Håvard S
2010-02-23 11:33:16
+1
A:
Use a FULL OUTER JOIN:
select a.col_a,
a.col_b,
b.col_c
from
(select col_a,col_bfrom tab1) a
join (select col_a,col_cfrom tab2) b on a.col_a= b.col_a
p2u
2010-02-23 11:37:00
You probably need something like `COALESCE(a.col_a, b.col_a) AS col_a` in the projection list.
araqnid
2010-02-23 11:41:00