Link to home
Start Free TrialLog in
Avatar of WestSoft
WestSoftFlag for United States of America

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.)

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

Open in new window


-------------------------
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 

Open in new window


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	

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of tigin44
tigin44
Flag of Türkiye image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of WestSoft

ASKER

That got me where I needed to go.  Thanks.  Didn't realize that I could use a SELECT statement to create a FROM source.  Perfect!  Thanks for teaching me.

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.