V
Vacation's Over
I am having difficulty adding criteria to a nested series of left joins
I want to do this in SQL as the specifics are altered on the fly.
I want to left join "AFTER" the criteria reduce each of the tables, but I
can only get the WHERE(s) to work after the joins have combined the tables
thus defeating the left join.
My code is rather complex so to limit this to one specific question, I am
posting a section of Allen Browne's pedigree code (nested Left joins - Self
Join) and asking how would you add criteria to this so that only the names of
horses WHERE [currenttable].Color = 'brown" would show up and other cells
would be null?
I have been trying to add these lines
WHERE DAM.Color = 'brown"
WHERE Sire.Color = "Brown"
I think that it's just a syntax isue but don't they need to be before the ON
statements to allow horses without "brown" parents to still print out but
with null parent names?
SELECT DISTINCTROW TblHorses.Name, Sire.Name, Dam.Name _
FROM (TblHorses_
LEFT JOIN TblHorses AS Sire _ '<======Tried here
ON TblHorses.SireID = Sire.ID) _
LEFT JOIN TblHorses AS Dam _ '<======Tried here
ON TblHorses.DamID = Dam.ID _'<======get incorrect results from here
ORDER BY TblHorses.Name;
THANKS ALL
I want to do this in SQL as the specifics are altered on the fly.
I want to left join "AFTER" the criteria reduce each of the tables, but I
can only get the WHERE(s) to work after the joins have combined the tables
thus defeating the left join.
My code is rather complex so to limit this to one specific question, I am
posting a section of Allen Browne's pedigree code (nested Left joins - Self
Join) and asking how would you add criteria to this so that only the names of
horses WHERE [currenttable].Color = 'brown" would show up and other cells
would be null?
I have been trying to add these lines
WHERE DAM.Color = 'brown"
WHERE Sire.Color = "Brown"
I think that it's just a syntax isue but don't they need to be before the ON
statements to allow horses without "brown" parents to still print out but
with null parent names?
SELECT DISTINCTROW TblHorses.Name, Sire.Name, Dam.Name _
FROM (TblHorses_
LEFT JOIN TblHorses AS Sire _ '<======Tried here
ON TblHorses.SireID = Sire.ID) _
LEFT JOIN TblHorses AS Dam _ '<======Tried here
ON TblHorses.DamID = Dam.ID _'<======get incorrect results from here
ORDER BY TblHorses.Name;
THANKS ALL