Link to home
Create AccountLog in
Avatar of williak7
williak7Flag for United States of America

asked on

joining multiple tables

I would like to see the detail records like the following for one or more users..

user               date                  credits   debits
John Doe      somedate                            10
John Doe      somedate                            20          
John Doe      somedate               25          
John Doe      somedate                           20


TABLES ARE STRUCTURED AS FOLLOWS:
Users (userid, username)
Credits (creditid, date, userid, amount)
Debits (debitid, date, userid, amount)


I can write a simple query to get just the credits and just the debits like the following...
SELECT username, date, amount as Credit FROM Credits
RESULTS.......................
John Doe      2006-04-20 13:09:00      15.00
John Doe      2006-04-20 13:09:00      10.00
John Doe      2006-04-20 13:09:00      1000.00
John Doe      2006-04-20 13:09:00      20.00
John Doe      2006-04-20 13:09:00      23.00

OR

SELECT username, date, amount as Debit FROM Debits
RESULTS.......................
John Doe      2006-04-08 00:00:00      400

-------------------------------------------------------------------------------------------------------
BUT when I try to get the records from both tables (which I know this is not correct) as either the joins are not correct or there should be some nesting of tables or something.

SELECT username, c.date, d.date c.amount, d.amount
FROM Users u
LEFT OUTER JOIN Credits c on c.userid = u.userid
LEFT OUTER JOIN Debits d on d.userid = u.userid

RESULTS.......................
username            c.date                                     d.date                  c.amount        d.amount
John Doe      2006-04-08 00:00:00      2006-04-20 13:09:00      15.00      400
John Doe      2006-04-08 00:00:00      2006-04-20 13:09:00      10.00      400
John Doe      2006-04-08 00:00:00      2006-04-20 13:09:00      1000.00      400
John Doe      2006-04-08 00:00:00      2006-04-20 13:09:00      20.00      400
John Doe      2006-04-08 00:00:00      2006-04-20 13:09:00      23.00      400
-------------------------------------------------------------------------------------------------------
ASKER CERTIFIED SOLUTION
Avatar of Patrick Matthews
Patrick Matthews
Flag of United States of America image

Link to home
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
See answer