Sql: select in or ALL

Question

0.00/5 (No votes)

See more:

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:

C#

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.

Posted 6-Jun-21 8:03am

csrss

Updated 6-Jun-21 8:10am

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Wendelius · Accepted Answer · 2021-06-06T08:10:00

Solution 1

If I understand your question correctly, you could try something like

SQL

-- select existing rows based on table2
select *
from table1 t1
where exists (select 1
              from table2 t2
              where t2.id = t1.id)
union
-- select all rows if table 2 is empty
select *
from table1 t1
where 0 = (select count(*) 
           from table2)

Posted 6-Jun-21 8:10am

Wendelius

Updated 6-Jun-21 8:43am

v2

Comments

csrss 7-Jun-21 2:17am

Thanks. Will try that out

csrss 7-Jun-21 2:36am

Thanks! This helped, I had to adjust it to my needs of course but this is it :)

Wendelius 7-Jun-21 23:17pm

Glad you got it solved