williak7
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
-------------------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- -------
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
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.