Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 175
  • Last Modified:

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
-------------------------------------------------------------------------------------------------------
0
williak7
Asked:
williak7
1 Solution
 
Patrick MatthewsCommented:
SELECT u.username, c.[date], c.amount AS credits, NULL AS debits
FROM Users u INNER JOIN
    Credits c ON u.userid = c.userid
UNION ALL
SELECT u.username, d.[date], NULL AS credits, d.amount AS debits
FROM Users u INNER JOIN
    Debits d ON u.userid = d.userid
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now