Sql aggregation of selection results
I would like to combine all my selection results:
I have select user result1 from....)
one returning 2 columns:
user results1
**** ********
user1 5
user2 8
I also have another query select user result2 from....)
returning 2 more columns:
user results2
**** ********
user1 9
user2 5
user3 15
How do I join the results along with 3 columns to do something like:
user results1 results2
**** ******** ********
user1 5 9
user2 8 5
user3 15
source to share
If you are not sure if a value User
exists in both tables, you should use FULL JOIN
to query the tables. A FULL JOIN
or FULL OUTER JOIN
( OUTER
optional) will return rows from both tables, but if User
doesn't exist in one of them, you will still get the results:
select
[user] = coalesce(r1.[user], r2.[user]),
r1.results1,
r2.results2
from result1 r1
full join result2 r2
on r1.[user] = r2.[user];
See SQL Fiddle with Demo .
The problem with usage INNER JOIN
is what User
is in both tables. If you try to use RIGHT JOIN
or LEFT JOIN
- then you still cannot return all users and their results.
If you have multiple queries that you are trying to combine, you can use CTEs or subqueries:
;with query1 as
(
select [user], result1 from results1
),
query2 as
(
select [user], result2 from results2
)
select
[user] = coalesce(q1.[user], q2.[user]),
q1.result1,
q2.result2
from query1 q1
full join query2 q2
on q1.[user] = q2.[user]
source to share