CODEWITHSUNDEEP
sqlsql-serverselect
mpora
mpora

Reputation: 1479

Combine results of two SQL selects

I have the following two SQL selects:

select 
    u.ID, name, "Desc", sum(pp.amount) as paid
from 
    [dbo].[Users] u, [dbo].[UserTypes] ut, [dbo].[PlayerPayments] pp
where 
    u.UserTypeID = ut.ID 
    and u.ID = pp.UserID
group by 
    u.ID, Name, "Desc";

select 
    u.ID,name, "Desc", sum(ga.GamePrice) as owed, count(ga.ID) as gamesplayed
from 
    [dbo].[Users] u,[dbo].[UserTypes] ut, [dbo].[Games] ga, [dbo].[GamePlayers] gp
where 
    u.UserTypeID = ut.ID 
    and u.ID = gp.UserID 
    and gp.GameID = ga.ID
group by 
    u.ID, Name, "Desc";

And they return the following results look like as follows:

Returned result

How can I combine the results together by adding the two columns (owed and gamesplayed) on the first result set?

Upvotes: 0

Views: 65

Answers (2)

Mudassir Hasan
Mudassir Hasan

Reputation: 28771

You will have to use UNION ALL or UNION (if you want to remove duplicates) and pass default value for extra columns in query 1

select u.ID,name, "Desc", sum(pp.amount) as paid, 0 as owed, 0 as gamesplayed
from [dbo].[Users] u,[dbo].[UserTypes] ut, [dbo].[PlayerPayments] pp
where u.UserTypeID = ut.ID and u.ID = pp.UserID
group by u.ID,Name,"Desc";
UNION ALL
select u.ID,name, "Desc", sum(ga.GamePrice) as owed, count(ga.ID) as gamesplayed
from [dbo].[Users] u,[dbo].[UserTypes] ut, [dbo].[Games] ga, [dbo].[GamePlayers] gp
where u.UserTypeID = ut.ID and u.ID = gp.UserID and gp.GameID = ga.ID
group by u.ID,Name,"Desc";

Note Also the datatypes of extra column added in query 1 must match with columns in query 2 else UNION operation will throw error

Upvotes: 1

Paweł Dyl
Paweł Dyl

Reputation: 9143

This is a generic solution:

SELECT T1.*, T2.owed, T2.gamesplayed FROM
(
  select u.ID,name, "Desc", sum(pp.amount) as paid
  from [dbo].[Users] u,[dbo].[UserTypes] ut, [dbo].[PlayerPayments] pp
  where u.UserTypeID = ut.ID and u.ID = pp.UserID
  group by u.ID,Name,"Desc"
) T1
JOIN
(
  select u.ID,name, "Desc", sum(ga.GamePrice) as owed, count(ga.ID) as gamesplayed
  from [dbo].[Users] u,[dbo].[UserTypes] ut, [dbo].[Games] ga, [dbo].[GamePlayers] gp
  where u.UserTypeID = ut.ID and u.ID = gp.UserID and gp.GameID = ga.ID
  group by u.ID,Name,"Desc"
) T2
ON T1.ID=T2.ID

Upvotes: 3

Related Questions