I have the following SELECT statement
SELECT MU.SUBID,
MU.LOCID,
MU.MENUCAT,
MU.MENUNAME,
MU.RECIPENAME,
RC.HAS_NUTRIENT S
FROM FR_MENUS AS MU
LEFT OUTER JOIN FR_RECIPES AS RC
ON MU.SUBID=RC.SUB ID AND MU.LOCID=RC.LOC ID AND MU.RECIPENAME=R C.RECIPENAME
WHERE MU.SUBID='NH102 00901' AND MU.LOCID='WestJ eff'
The PK of MU table is SUBID,LOCID,MEN UCAT,MENUNAME. This query was been returning records in that sequence.
After adding a new field to the RC table, this query now returns records in a different sequence. I then removed the new field from RC and still have the problem.
Also, removing the WHERE clause returns records in the correct sequence.
ORDER BY SUBID,LOCID,MEN UCAT,MENUNAME fixes the problem, but should not be necessary.
Can anyone explain what is happening?
SELECT MU.SUBID,
MU.LOCID,
MU.MENUCAT,
MU.MENUNAME,
MU.RECIPENAME,
RC.HAS_NUTRIENT S
FROM FR_MENUS AS MU
LEFT OUTER JOIN FR_RECIPES AS RC
ON MU.SUBID=RC.SUB ID AND MU.LOCID=RC.LOC ID AND MU.RECIPENAME=R C.RECIPENAME
WHERE MU.SUBID='NH102 00901' AND MU.LOCID='WestJ eff'
The PK of MU table is SUBID,LOCID,MEN UCAT,MENUNAME. This query was been returning records in that sequence.
After adding a new field to the RC table, this query now returns records in a different sequence. I then removed the new field from RC and still have the problem.
Also, removing the WHERE clause returns records in the correct sequence.
ORDER BY SUBID,LOCID,MEN UCAT,MENUNAME fixes the problem, but should not be necessary.
Can anyone explain what is happening?
Comment