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

Is this what you want:


SQL> create table x (parent number);

Table created.

SQL> create table y (child number, app char);

Table created.

SQL> insert into x values (1);

1 row created.

SQL> insert into x values (2);

1 row created.

SQL> insert into x values (3);

1 row created.

SQL> insert into y values (1,'A');

1 row created.

SQL> insert into y values (1,'D');

1 row created.

SQL> insert into y values (2,'D');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from x,y where x.parent=y.child and y.app='A'
2 union
3 select * from x,y where x.parent=y.child and y.app='D'
4 and y.child not in (select child from y where y.app='A');

PARENT CHILD A
---------- ---------- -
1 1 A
2 2 D

SQL>

Regards Andre Broers