If I am understanding your question correctly- you could also try something
like the following:
select ta.au_id
,ta.au_type
,ta_i.cli_id
,ta_i.cli_name
from TBL_AUTH ta
,(select distinct b.cli_id, b.cli_name
from TBL_AUTH a
,TBL_CLIENT b
where a.AU_TYPE ='MF'
and a.cli_id = b.cli_id) ta_i
where ta.cli_id = ta_i
Haven't run it - but done something similar this way. Hope that helps. You
could of course do the same thing with a sub query in the <where> clause, as
already shown, test the 2 for performance, see which is better.
--
Mike
On Thu, Oct 30, 2008 at 12:01 AM, 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
>
> >
>
--
Michael Mann
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
