Michel,

Your last SELECT had a typo ("CHILD ON" missing) but it works. It produces the same result as Andrew's solution except that instead of the placeholder values for parent records that have no children, you get .null. values.

SELECT PARENT.ID,CHILD.ID FROM PARENT LEFT JOIN CHILD ON PARENT.ID=CHILD.PARENTID WHERE CHILD.MYFIELD=0

HTH

<
By using a standard SQL like the following, we obtain all combinations of parent/child records.

SELECT PARENT.ID,CHILD.ID FROM PARENT INNER JOIN PARENT.ID=CHILD.PARENTID

If we use a LEFT JOIN, we would end up with all all combinations of parent/child records + those parents which do not have a child record.

SELECT PARENT.ID,CHILD.ID FROM PARENT LEFT JOIN PARENT.ID=CHILD.PARENTID

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?>>

Daniel