?
Solved

Detail transactions with multiple tables

Posted on 2007-10-03
18
Medium Priority
?
169 Views
Last Modified: 2010-03-19
I am trying to show the detail records for each transaction for debits and credits.  Tables are as follows:
Users (userid, username)
Credits (creditid, userid, amount)
Debits (debitid, userid, amount)

How do I show it in a table as follows.
Username = "John Doe"

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

0
Comment
Question by:williak7
  • 6
  • 5
  • 5
  • +1
18 Comments
 
LVL 8

Expert Comment

by:MrRobot
ID: 20011832
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

0
 
LVL 30

Expert Comment

by:nmcdermaid
ID: 20011867
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

0
 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
ID: 20012666
<<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...
0
Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

 
LVL 8

Expert Comment

by:MrRobot
ID: 20013432
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.
0
 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
ID: 20013551
<<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...;)
0
 

Author Comment

by:williak7
ID: 20013586
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.
0
 

Author Comment

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

0
 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
ID: 20014211
Use FULL OUTER JOIN between the debit and credit tables (on day and userid)

Hope this helps...
0
 

Author Comment

by:williak7
ID: 20014382
racimo, could you show me what you mean?
0
 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
ID: 20014861
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...
0
 

Author Comment

by:williak7
ID: 20017732
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
-------------------------------------------------------------------------------------------------------
0
 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
ID: 20018112
Have you tried the solution proposed?
0
 

Author Comment

by:williak7
ID: 20018181
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.
0
 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
ID: 20018243
<<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)...
0
 
LVL 30

Expert Comment

by:nmcdermaid
ID: 20020421
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
0
 
LVL 8

Expert Comment

by:MrRobot
ID: 20020755
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]
0
 
LVL 8

Accepted Solution

by:
MrRobot earned 2000 total points
ID: 20020785
there are two unused words in my last post, so it's just,


select
      u.username, [date], credit, 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]
0
 
LVL 8

Expert Comment

by:MrRobot
ID: 20020817
if you don't want zeros, just replace them with null.
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Ready to get certified? Check out some courses that help you prepare for third-party exams.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question