Joining 2 Tables

BlueGemini · #1 (**permalink**) 07-03-08, 01:40

Could somebody please help me with this....

I'm about to join 2 tables...

Table1
User NoOfPrintsInP1 Time
--------------------------------------------
James 5 18:00
James 6 18:30
Joshua 1 18:35
Mo 5 18:55

Table2
User NoOfPrintsInP2 Time
--------------------------------------------
James 1 15:00
James 2 15:05
Joshua 3 15:30
James 5 16:00

I suppose to do a query that outputs the total number of both NoOfPrints per User. It should be something like this...

User NoOfPrintsInP1 NoOfPrintsInP2
-----------------------------------------------------
James 11 8
Joshua 1 3
Mo 5 0

I did a Inner Join but it didn't output the way I expect and the numbers are wrong... what went wrong?

any ideas?

r937 · #2 (**permalink**) 07-03-08, 08:19

Code:

SELECT User
     , SUM(p1) AS NoOfPrintsInP1
     , SUM(p2) AS NoOfPrintsInP2
  FROM (
SELECT User
     , SUM(NoOfPrintsInP1) AS p1
     , 0 AS p2
  FROM Table1
GROUP BY User
UNION ALL
SELECT User
     , 0 
     , SUM(NoOfPrintsInP2) AS p2
  FROM Table2
GROUP BY User
) AS d
GROUP BY User

Internet Services

Web Development

Digital Marketing

Consumer Tech