Detail transactions with multiple tables

since they are listed as two different tables, which is presented like columns, it should be done in the client side, listing two tables; credits and debits for the user. it can be done in the sql side using temporary tables etc but would be a bad practice.

select
c.amount credit
from
credits c
join users u on u.userid = c.userid
where
c.userid = xxx

select
d.amount debit
from
debits d
join users u on u.userid = d.userid
where
d.userid = xxx

Here is one method, but of course you will get a John Doe record per line. As MrRobot says, if you want any particular presentation it needs to be done on the client side.


SELECT UserName, SUM(Credit) As Credit, SUM(Debit) as Debit
FROM (
SELECT U.UserName, C.amount as Credit, 0 As Debit
FROM Credit C INNER JOIN Users U ON U.userid = C.userid
UNION ALL
SELECT U.UserName, 0 as Credit, D.Amount As Debit
FROM Credit D INNER JOIN Users U ON U.userid = C.userid
) A
ORDER BY UserName

<<Username = "John Doe"

user           credits   debits
John Doe      0          10
                    20           0
                    25           0
                     0           20>>

What logic allows you to place a specific credit on the same line than a specific debit (for instance why would 0 be placed on the same line than 10).  I think there is a missing requirement necessary to give you a response...

there seems to be no requirement, just like two different tables put nearby each other. that's why I think it should be done in the client.

<<there seems to be no requirement, just like two different tables put nearby each other. that's why I think it should be done in the client.>>
Maybe...But I find it hard to propose a solution without knowing...I do agree however that presentation should generally be done in client side...;)

I will use Reporting services to present the data and clean up the username.  There does not have to be a '0' in the column - it could be NULL.

Here is the query I started with, but it repeats data in the columns

SELECT usernmae, c.amount as credit, d.amount as debit
FROM Users u
LEFT OUTER JOIN Credits c on c.userid = u.userid
LEFT OUTER JOIN Debits d on d.userid = d.userid

I want the data to display like an itemized bill from a bank.

I should have included there is a date with each transaction so the credit and debit tables would each have a date field.

Use FULL OUTER JOIN between the debit and credit tables (on day and userid)

Hope this helps...

racimo, could you show me what you mean?

Assuming the following structure...(I added the date column in each of debit and credit columns)

Users (userid, username)
Credits (creditid, userid, amount, date)
Debits (debitid, userid, amount, date)

Sorry but no time to debug but that may put you on the track or t least propose a solution...

select
case D.day_transaction when NULL then C.day_transaction else D.day_transaction end as day_transaction,
case D.userid when NULL then C.userid else D.userid end as userid,
case D.userid when NULL then 0 else D.sum_debit end as debit,
case C.userid when NULL then 0 else C.sum_credit end as credit
from
(select convert(varchar(10), date, 101) as day_transaction, userid, sum(amount) as sum_debit from debit group by convert(varchar(10), date, 101), userid) D
full outer join
(select convert(varchar(10), date, 101) as  day_transaction, userid, sum(amount)  as sum_credit from credit group by convert(varchar(10), date, 101), userid) C
on D.day_transaction = C.day_transaction

Hope this helps...

I am going to try and give better information and start this question over....
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
-------------------------------------------------------------------------------------------------------

Have you tried the solution proposed?

Racima, no that did not work.  It returns duplicates in either the debit or credit columns.
I didn't think this query would be so difficult.

<<I didn't think this query would be so difficult.>>
Sorry but dealing with presentation issues at SQL is never practical...I will take a look at it tomorrow...(time here is 11:50 pm)...

Joining will always return 'duplicates' if the joined columns in either table are not unique. So try the union again (This time I remembered the group by):


SELECT UserName, [date], SUM(Credit) As Credit, SUM(Debit) as Debit
FROM (
SELECT U.UserName, C.[Date], C.amount as Credit, 0 As Debit
FROM Credit C INNER JOIN Users U ON U.userid = C.userid
UNION ALL
SELECT U.UserName, C.[Date], 0 as Credit, D.Amount As Debit
FROM Credit D INNER JOIN Users U ON U.userid = C.userid
) A
GROUP BY UserName, [date]
ORDER BY UserName

Tested ;


select
      u.username, [date], credit credit, debit debit
from (
      select userid, [date], amount credit, 0 debit from credits
      union all
      select userid, [date], 0 credit, amount debit from debits
) t
join users u on u.userid = t.userid
order by username, [date]

if you don't want zeros, just replace them with null.