Re: Dumb Oracle SQL question

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_at_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
Received on Tue Sep 05 2006 - 12:08:05 CDT