mpora mpora - 2 months ago 6
SQL Question

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?

Answer

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
Comments