Just want to add to Sergey' replay. Compare
SELECT ;
crsPMPlan.IDNum, ;
crsPMPlan.PlanID, ;
crsPMPlan.ContID, ;
crsPMPlan.UserID, ;
crsPMPlan.Pin ;
FROM crspmplan ;
WHERE ....
with
SELECT ;
crsPMPlan.IDNum, ;
crsPMPlan.PlanID, ;
crsPMPlan.ContID, ;
crsPMPlan.UserID, ;
crsPMPlan.Pin ;
FROM crspmplan, crsPMPlanH ;
WHERE ....
to see the difference even without JOIN.
>The 1st SQL returns 1 record like I expected, but why does the 2nd SQL return all the records in the crsPMPlan table? I would have expected the results to be the same.
>
>
>SELECT ;
> crsPMPlan.IDNum, ;
> crsPMPlan.PlanID, ;
> crsPMPlan.ContID, ;
> crsPMPlan.UserID, ;
> crsPMPlan.Pin ;
> FROM crspmplan ;
> WHERE ;
> (crspmplan.IDNum == crspmplanh.IDNum) AND ;
> (ALLTRIM(crspmplan.USERID) == ALLTRIM(crspmplanh.USERID)) AND ;
> (ALLTRIM(crspmplan.Pin) == ALLTRIM(crspmplanh.Pin)) ;
> GROUP BY ;
> crsPMPlan.IDNum, ;
> crsPMPlan.PlanID, ;
> crsPMPlan.UserID, ;
> crsPMPlan.Pin ;
> INTO CURSOR tmpWork
>
>
>
>
>SELECT ;
> crsPMPlan.IDNum, ;
> crsPMPlan.PlanID, ;
> crsPMPlan.ContID, ;
> crsPMPlan.UserID, ;
> crsPMPlan.Pin ;
> FROM crspmplan ;
> JOIN crsPMPlanH ON (crspmplan.IDNum == crspmplanh.IDNum) ;
> WHERE ;
> (ALLTRIM(crspmplan.USERID) == ALLTRIM(crspmplanh.USERID)) AND ;
> (ALLTRIM(crspmplan.Pin) == ALLTRIM(crspmplanh.Pin)) ;
> GROUP BY ;
> crsPMPlan.IDNum, ;
> crsPMPlan.PlanID, ;
> crsPMPlan.UserID, ;
> crsPMPlan.Pin ;
> INTO CURSOR tmpWork
>
