I am trying to do a query, where I have 2 collections. 2 tables. 1 table contains some values, and another is either empty or contains a subset from a 1st table,So something like:
Table1
-------
v1
v2
v3
Empty Table2 OR Table2 with one value, for example v1
Now, I need to select results depending on the values presence in a second table, so:
select * from Table1 where id in (if value is present in Table2 - return only this value, if not - return everything from Table1)
What I have tried:
I have tried some queries, but basically have no idea how to do this. This is done easily with LINQ to SQL:
result = Table1.Where(x => !Table2.Any() || Table2.Select(t => t.id).Contains(x.id))
But how to do it in sql I have no idea. Tried something like:
select * from [dbo].[Table1] s where s.id in (case when exists(select 1 from [dbo].[Tabl1]) then select ss.id from [dbo].[Table1] ss else select sss.id from [dbo].[Table1] sss end)
For now trying just to understand how to do it conditionally so trying just with one table.