>>Now, I would like to keep this ability of having those parent records which do not have a child record when we have to add a condition for the child record. Basically, I would need something like this:
>>
>>SELECT PARENT.ID,CHILD.ID FROM PARENT LEFT JOIN PARENT.ID=CHILD.PARENTID WHERE CHILD.MYFIELD=0
>>
>>However, this doesn't work because all parent records who do not have a child record do not meet that condition so they won't be listed in. How can I achieve that?
>
>In this situation, where the filter affects the child of an outer join, you need to add the filter condition to the join clause. That way, it's used in determining which records have a match and, therefore, which records come into the result because of the outer join. Here's the query you need:
>
>SELECT PARENT.ID,CHILD.ID ;
> FROM PARENT ;
> LEFT JOIN PARENT.ID=CHILD.PARENTID AND CHILD.MYFIELD=0
>
>When the filter condition is on the parent, you can leave it in the WHERE clause.

Thanks a lot, this works great.