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?