Hai ,
Use this query
SELECT A.CLI_ID,A.AU_ID,A.AU_TYPE , B.CLI_NAME
FROM TBL_AUTH A ,TBL_CLIENT C
WHERE A.CLI_ID = C.CLI_ID
AND A.CLI_ID IN(
SELECT B.CLI_ID
FROM TBL_AUTH B
WHERE B.AU_TYPE = 'MF'
)
Thanks & Regrads
Binu.K.S
On 10/30/08, allenjames <[EMAIL PROTECTED]> wrote:
>
>
> Could any one help me create a sub query for the following I am woring
> on Oracle 10g
>
> From the folloing two table I would like the output containg all the
> autos where any Client who has a AU_TYPE of MF should be listed along
> with other related AU_TYPE i.e. SS-1, SS-2. Excluding any client who
> do not have AU_TYPE of MF.
>
> TABLE1 TBL_CLIENT
> CLI_ID CLI_NAME
> ----------------------------------------------
> 1 Jay Doe
> 2 Allen Amm
> 3 Peter Saan
> 4 Mali Zaski
> 5 Sara Adam
>
> TABLE2: TBL_AUTH
> AU_ID AU_TYPE CLI_ID
> ----------------------------------------------------
> 1 MF 1
> 2 SS-1 1
> 3 MF 2
> 4 SS-1 3
> 5 MF 4
> 6 SS-2 4
> 7 SS-1 5
> 8 SS-3 5
>
> FOLLOWING ARE THE RESULT FROM A SUB QUERY USING ABOVE TWO TABLES:
> OUTPUT REQUIRED
> CLI_ID AU_ID AU_TYPE CLI_NAME
> -------------------------------------------------------------------
> 1 1 MF Jay Doe
> 1 2 SS-1 Jay Doe
> 2 3 MF Allen Amm
> 4 5 MF Mali Zaski
> 4 6 SS-2 Mali Zaski
>
> Thanks
>
> >
>
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google
Groups "Oracle PL/SQL" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to
[EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/Oracle-PLSQL?hl=en
-~----------~----~----~----~------~----~------~--~---
