Re: SQL question

Try the same query with the last term of WHERE clause slightly changed. The idea is the same suggested by Wilco. Greetings

SELECT A.* , B.LIC_K_ID, B.BROKER_ID, B.OWNER,          C.DSCR LIC_TYPE_DSCR,D.LIC_STATUS_TYPE_ID, E.DSCR LIC_STATUS_DSCR

         FROM LIC A, LIC_K B, LIC_TYPE C, LIC_STATUS D,
                    LIC_STATUS_TYPE E
         WHERE B.BROKER_ID=:BROKER_ID AND
                        A.LIC_ID=B.LIC_ID AND
                        C.LIC_TYPE_ID=A.LIC_TYPE_ID AND
                        D.LIC_ID(+)=B.LIC_ID AND
                        E.LIC_STATUS_TYPE_ID(+)=D.LIC_STATUS_TYPE_ID AND
                       ( D.LIC_STATUS_ID=( SELECT MAX( F.LIC_STATUS_ID )
                                              FROM LIC_STATUS F
                                              WHERE F.LIC_ID=B.LIC_ID) OR
                          D.LIC_STATUS_ID IS NULL );

Jim Poe wrote:

> In the following select I am trying to retrieve a list of LIC
> information. The LIC table has a detail table LIC_STATUS. I'm only
> interested in retrieving one row per license. The outer joins allow me
> to retrieve rows without any status records. The subselect allows me to
> retrieve only one row for any license with multiple status rows. My
> problem is that the subselect overrides my outer joins and won't return
> a LIC row if there are no LIC_STATUS rows. Is this possible?
>
> SELECT A.* , B.LIC_K_ID, B.BROKER_ID, B.OWNER,
> C.DSCR LIC_TYPE_DSCR,D.LIC_STATUS_TYPE_ID, E.DSCR
> LIC_STATUS_DSCR
> FROM LIC A, LIC_K B, LIC_TYPE C, LIC_STATUS D,
> LIC_STATUS_TYPE E
> WHERE B.BROKER_ID=:BROKER_ID AND
> A.LIC_ID=B.LIC_ID AND
> C.LIC_TYPE_ID=A.LIC_TYPE_ID AND
> D.LIC_ID(+)=B.LIC_ID AND
> E.LIC_STATUS_TYPE_ID(+)=D.LIC_STATUS_TYPE_ID AND
> D.LIC_STATUS_ID=( SELECT MAX( F.LIC_STATUS_ID )
> FROM LIC_STATUS F
> WHERE F.LIC_ID=B.LIC_ID);
>
> Thanks
>
> --
> Jim Poe
> <jpoe_at_fulcrumit.com>
> Fulcrum InteTech, Inc.
Received on Tue Jul 20 1999 - 18:26:25 CDT