Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations IamaSherpa on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Left Join 1

Status
Not open for further replies.

OnDOT

Programmer
Jul 20, 2001
11
SG
I would like to do a left join on the from Pymnt to Commission 3 times in MS ACCESS.
But no matter how I try, I can't get it right.

Table 1: Pymnt
PymntID Amt
1 10
2 20
3 30

Table 2: Commission
PymntID Type PCT
1 AGT 70
1 COM 20
1 MGR 10
2 AGT 80
2 COM 10
2 MGR 10
3 AGT 90
3 COM 10

The desired result after the join is:

1 AGT 70 COM 20 MGR 10
2 AGT 80 COM 10 MGR 10
3 AGT 90 COM 10


Can someone enlighten me how should the SQL be?

Thanks.
Eric

 

Here is one possible solution.

Select p.PymntID, c1.Type As Type1, c1.Pct As PCT1, c2.Type As Type2, c2.Pct As PCT2, c3.Type As Type3, c3.Pct As PCT3
FROM Pymnt p
LEFT JOIN (Select * From Commission Where Type="AGT") As c1 ON p.PymntID = c1.PymntID
LEFT JOIN (Select * From Commission Where Type="COM") As c2 ON p.PymntID = c2.PymntID
LEFT JOIN (Select * From Commission Where Type="MGR") As c3 ON p.PymntID = c3.PymntID
Terry L. Broadbent
faq183-874 contains some tips and ideas for posting questions in these forums. Please review it and comment if you have time.
NOTE: Reference to the FAQ is part of my signature and is not directed at any individual.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top