Hi Michael,

SELECT Client.Numero, Province.Numero AS Province_Numero
 FROM Client OUTER APPLY (SELECT Province.Numero FROM Province WHERE Province.Numero=Client.NoProvince AND Province.Active=1) Province

SELECT Client.Numero, 
             (SELECT Province.Numero FROM Province WHERE Province.Numero=Client.NoProvince AND Province.Active=1) AS Province_Numero
 FROM Client

MartinaJ


>I have a select to do to retrieve records from a table where one of the where clause field primary key of a field exists in a sub table with its related condition.
>
>So, we would usually do this like this:
>
>

>SELECT Client.Numero
> FROM Client
> WHERE Client.NoProvince IN (SELECT Province.Numero FROM Province WHERE Client.NoProvince=Province.Numero AND Province.Active=1)
>

>
>Now, I need to expand that to support the possibility that the client table might not always have a province value. If that is the case, I need to collect that record as well.
>
>So, basically, I would like to avoid doing a UNION. Would there be a way with such select to add support for such need?