I want to create a statement like the following:
if (select statement1 returns rows){
select statement2
}
else {
select statement3
}
This if statement is part of a bigger query
select from products where p.id in (
if (select statement1 returns rows){
select statement2
}
else {
select statement3
}
Is this possible in SQL Server, or is my thinking incorrect?
You need to use a combination of EXISTS and CASE..END:
select *
from products
where p.id in (
case when (exists ( <statement1> ))
then ( <statement2> )
else ( <statement3> )
end
)
Where <statement1> might be: SELECT * FROM Customer WHERE Id = 123
<statement2> might be: SELECT MIN(field) FROM someTable
<statement3> might be: SELECT 0 as DefaultValue
If you can show some examples of what you want those actual statements to be I can provide a more concrete answer.
Actually SQL is more rigid, due to performance considerations. Your query will look more like this:
select *
from products
where 1 = case when (exists ( <statement1> ))
then case when p.id in ( <statement2> ) then 1 else 0 end
else case when p.id in ( <statement3> ) then 1 else 0 end
end
Since you are checking for the same id existance, I think you could do a UNION here.
select *
from products
where p.id in (select id from table1
union
select id from table2)
I'd recommend using a UNION between your two conditions to help avoid performance problems...and may be easier to read as well.
SELECT FROM products p
WHERE EXISTS (SELECT STATEMENT1)
AND EXISTS (SELECT STATEMENT2 s2 WHERE s2.p_id = p.id)
UNION
SELECT FROM products p
WHERE NOT EXISTS (SELECT STATEMENT1)
AND EXISTS (SELECT STATEMENT3 s3 WHERE s3.p_id = p.id)
Depending on the nature of the statements, you might be able to leave off the Not Exists in the second select.
