I'm trying to do something like this :
from table1 , (select X , Y , Z from table2 WHERE clause condition ) SUB
where table1.B = 0 and table1.C = 1 and
if sub.X = 1 => table1.Y = SUB.Y
elseif sub.X = 0 => table1.Y not in (select Y from table2 WHERE clause condition)
else if there is no result in the sub query , no join with sub, return all the result from table1....)
in fact I have a table of product. (table1)
and I have another table (table2) where I managed the right access for a customer.
if there is no entry in table2, no restriction for the customer in table1 (last else)
if there is an entry in table2, there is a restriction for the customer for specific product.
there is 2 type of restriction, in the X column (bit).
0 = no access for this product (but yes access to other product)
1 = only access for this product (so no access for other product)
Many thanks for your help.