WestSoft
asked on
How do I join Transaction Detail and Transaction Payment tables into one table?
I need to figure out the SQL to join three tables to produce one resulting merged table. I have as source tables Header, Detail and Payments. I'd like to merge them into a table called Merged. This is basically sales transaction data where each transaction (Header) can have many line items (Detail) and many forms of payment (Payments). As an oddity, the TotalTax for each transaction is stored in the Header table rather than the Detail table.
Sample data and my SQL attempts follows: (I retyped this all so forgive any errors. I think it accurately represents my challenge.)
-------------------------
PSUEDO OF WHAT I WANT:
-------------------------
Insert the following into Merged
TranId as h.TranId
Total as h.TotalTax + [sum of all d.ItemAmt where h.TranId = d.TranId]
Pmts as [sum of all p.PmtAmt where h.TranId = p.TranId]
Diff as m.Detail - m.Pmts
------------------------
MY FAILED ATTEMPT:
------------------------
I also tried using INNER JOINS but got the same results. The payments amount ends up being wrong. I can write two separate SQL statements to correctly get the data I want from (a) just the Detail table and (b) just the Payments table. But I'm having a hard time getting that into a single statement. So the following gets what I want but in two tables.
-------------------------- ---------- ---------- -----
WORKS TO GET DATA I WANT BUT IT ENDS UP IN TWO TABLES.
HOW DO I BOIL THIS DOWN TO ONE STATMENT THAT PRODUCES ONE TABLE?
-------------------------- ---------- ---------- -----
Sample data and my SQL attempts follows: (I retyped this all so forgive any errors. I think it accurately represents my challenge.)
Header h
----------
TranId TotalTax
1 1.15
2 2.01
3 0.13
4 5.14
5 1.01
Detail d
----------
ItemId TranId ItemAmt
1 1 2.99
2 1 0.55
3 1 4.99
4 2 1.99
5 2 8.49
6 3 1.19
7 4 25.15
8 5 10.99
Payments p
----------
PmtId TranId PmtAmt Type
1 1 9.68 Cash
2 2 10.00 Cash
3 2 2.49 Credit
4 3 1.32 Debit
5 4 30.47 Credit
6 5 6.00 Cash
7 5 6.00 Credit
Merged m
----------
TranId Total PmtAmt Diff
1 9.68 9.68 0.00
2 12.49 12.48 0.01
3 1.32 1.32 0.00
4 30.29 30.47 -0.18
5 12.00 12.00 0.00
-------------------------
PSUEDO OF WHAT I WANT:
-------------------------
Insert the following into Merged
TranId as h.TranId
Total as h.TotalTax + [sum of all d.ItemAmt where h.TranId = d.TranId]
Pmts as [sum of all p.PmtAmt where h.TranId = p.TranId]
Diff as m.Detail - m.Pmts
------------------------
MY FAILED ATTEMPT:
------------------------
SELECT h.TranId AS TranId,
SUM(d.ItemAmt) + SUM(DISTINCT(h.TotalTax)) AS Total,
SUM(p.PmtAmt) AS PmtAmt
FROM Header h,
Detail d,
Payments p
WHERE h.TranId = d.TranId AND h.TranId = p.TranId
GROUP BY h.TranId
I also tried using INNER JOINS but got the same results. The payments amount ends up being wrong. I can write two separate SQL statements to correctly get the data I want from (a) just the Detail table and (b) just the Payments table. But I'm having a hard time getting that into a single statement. So the following gets what I want but in two tables.
--------------------------
WORKS TO GET DATA I WANT BUT IT ENDS UP IN TWO TABLES.
HOW DO I BOIL THIS DOWN TO ONE STATMENT THAT PRODUCES ONE TABLE?
--------------------------
SELECT h.TranId AS TranId,
SUM(p.PmtAmt) AS PmtAmt
FROM Header h
INNER JOIN Payments p ON p.TranId = h.TranId
GROUP BY h.TranId
GO
TranId PmtAmt
1 9.68
2 12.48
3 1.32
4 30.47
5 12.00
-------------
SELECT h.TranId AS TranId,
SUM(d.ItemAmt) + SUM(DISTINCT(h.TotalTax)) AS Total
FROM Header h
INNER JOIN Detail d ON d.TranId = h.TranId
GROUP BY h.TranId
GO
TranId Total
1 9.68
2 12.49
3 1.32
4 30.29
5 12.00
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
NOTE to others that use this great solution. Remove the 'AS PmtAmt ' from line 4 and this is good to go. Just a small typo.