I am going to try these examples. They aren't working quite the way I
was thinking of.

this is part of a complex join. I want to so something like:

SELECT child.PK_ID
FROM parent, jointable, child
WHERE parent.type_id = jointable.type_id
AND jointable.type_id = child.type_id
AND Parent.status = 'D'

and this is where it gets tricky:

AND child.status = 'A' but if that value is null, then give me the
child.pk_id where the child.status = 'D'

I need to see one record only. I am just going to write this in the
application language if i can't figure out a simple SQL solution. I
got two records when I looked at the below examples, but I will rewrite
and see what transpires.

thanks!

Don


bbcrock@gmail.com wrote:
> This is annoying me. Been working on some other things and this
> complex query came back to bite me. Oracle 9i.
>
> I have a parent record and one or more child records.
>
> I want the query to join on the "Approved" record, (Status = 'A') where
> it exists and if it doesn' exist then join on the draft record (Status
> = 'D'). There is always one draft record, but there might be an
> approved record.
>
> I tried some attempts with subselects and NVLs, but they failed. I
> didn't save this code.
>
> Can anyone point me to Ask Tom or another example? I looked there
> first, but I'm using the wrong keywords.
>
> thanks,
>
> Don