Results 1 to 2 of 2
  1. #1
    Join Date
    Jun 2008
    Posts
    49

    Unanswered: Joining 2 Tables

    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?
    Last edited by BlueGemini; 07-03-08 at 02:45.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •