Using left or right join correctly in SQL

Question

I'm currently learning about left and right joins in class. It makes perfect sense when dealing with one-one or one-many tables but I can't figure out the many-many relationships. I'm supposed to display a list of how much money a golfer raised each year even if its 0. So I should be showing 5 golfers for all 3 years for a total of 15 rows. No matter the combination of left, right or full I do, I only get the result of the TEventGolfers table.

INSERT INTO TEvents ( intEventID, strEventDate )
VALUES   ( 1, '2015' )
        ,( 2, '2016' )
        ,( 3, '2017' )

INSERT INTO TGolfers( intGolferID, strFirstName, strLastName )
VALUES   ( 1, 'Trevor', 'Duplin')
        ,( 2, 'Jay', 'Graue')
        ,( 3, 'Mary', 'Beimesch')
        ,( 4, 'Tony', 'Hardan')
        ,( 5, 'Iwana', 'Bucks')

INSERT INTO TEventGolfers ( intEventGolferID, intEventID, intGolferID ) 
VALUES   (1, 1, 1)
        ,(2, 1, 2)
        ,(3, 1, 3)
        ,(4, 2, 4)
        ,(5, 2, 5)
        ,(6, 2, 1)
        ,(7, 3, 2)
        ,(8, 3, 3)
        ,(9, 3, 5)

INSERT INTO TEventGolferSponsors( intEventGolferSponsorID, intEventGolferID, intSponsorID, monPledgeAmount) 
VALUES   (1, 1, 1, 160.00)
        ,(2, 1, 1, 200.50)
        ,(3, 1, 2, 100.25)
        ,(4, 1, 3, 20.00)
        ,(5, 1, 4, 1500.00)
        ,(6, 1, 5, 220.20)
        ,(7, 1, 6, 120.00)
        ,(8, 1, 7, 2550.00)
        ,(9, 1, 1, 100.50)
        ,(10, 1, 2, 70.25)
        ,(11, 1, 3, 20.00)
        ,(12, 1, 4, 250.00)
        ,(13, 1, 5, 60.20)
        ,(14, 1, 6, 50.00)
        ,(15, 1, 7, 250.00)

-- Select Statements
SELECT TE.strEventDate, TG.strFirstName, ISNULL(SUM(TEGS.monPledgeAmount), 0) AS AmountRaised
FROM TGolfers AS TG

JOIN TEventGolfers AS TEG
ON TEG.intGolferID = TG.intGolferID

JOIN TEvents AS TE
ON TEG.intEventID = TE.intEventID

JOIN TEventGolferSponsors AS TEGS
ON TEGS.intEventGolferID = TEG.intEventGolferID

GROUP BY TG.strFirstName, TE.strEventDate
ORDER BY TE.strEventDate

Are you absolutely sure you understand your objective? Why would you want to return a golfer with a year that he/she didn't play? What is the exact wording of your assignment? — Tab Alleman, Nov 30 '18 at 21:29
I updated the post. Gordon Linoff worked for what I originally asked but when adding the donations it lists the same amount for all 3 years. — , Nov 30 '18 at 21:47
Hi. Learn what LEFT/RIGHT JOIN returns: INNER JOIN rows plus unmatched left/right table rows extended by NULLs. FULL JOIN returns INNER JOIN rows UNION ALL unmatched left & right table rows extended by NULLs. Always know what INNER JOIN you want as part of an OUTER JOIN. PS Constraints are not needed to query. — philipxy, Nov 30 '18 at 23:42
Absolute basics of debugging say: Show that your program does what you expect as it goes through (sub)expressions by saying what that is & showing that it actually does it via incremental output. On adding problem code that you can't fix, research (the manual & the web). Repeat, minimizing working & wrong code. Then ask re the (small) difference between working & non-working examples. PS Why are you grouping by date & only first name? What if two golfers have the same first name? — philipxy, Nov 30 '18 at 23:52

score 3 · Answer 1 · edited Nov 30 '18 at 21:29

This is more a CROSS JOIN problem then an outer join problem. First, you want to generate all combinations of golfers and years using CROSS JOIN. Then LEFT JOIN is then used to bring back results.

To see what I mean, run this query:

select e.strEventDate, g.strFirstName, g.strLastName
from tevents e cross join
     tgolfers g;

You will see that the result set has all the rows you want -- but not all the columns. Next, you can add a LEFT JOIN to TEventGolfers to get the additional information in that table.

Because this is a homework problem, I'll let you complete the query.

Using left or right join correctly in SQL

1 Answers1