Creating joins correctly
I have been asked to create a sales opportunity report which returns a selection of fields from OOPR, OPR1, OPR3, OPR4 and OCPR. For starters I have the following:
SELECT DISTINCT T0.OpprId, T0.CardCode, T0.CardName, T0.OpenDate, T0.PredDate, T2.ClosePrcnt, T4.ThreatLevl, T4.Memo, T4.Won, T1.U_Country, T1.U_Source, T3.Descript, T5.Name
FROM OOPR T0 INNER JOIN OCPR T1 ON T0.CprCode = T1.CntctCode INNER JOIN OPR1 T2 ON T0.OpprId = T2.OpprId INNER JOIN OOST T3 ON T0.StepLast = T3.Num, OPR3 T4 INNER JOIN OCMT T5 ON T4.CompetId = T5.CompetId
This does not include OPR4 yet. My first problem is that it returns multiple lines for each opportunity based around permutations of stage and competitor. I have tried INNER, LEFT and RIGHT joins but have the same problem. I'm obviously missing something fairly fundamental. If I then try to JOIN in OPR4 I lock up the system until it brings back 105,000 result! We currently have a total of 360 opportunities!
Thanks for your time