CODEWITHSUNDEEP
sqlsql-serverstored-procedures
SBB
SBB

Reputation: 8970

SQL AND Clause only if another value exists

I have a basic SQL query where I am selecting data from a core records table. I want to add an AND clause to my statement to filter out the results based on a table variable, only if data actually exists in there.

SELECT 
    * 
FROM 
    TABLE
WHERE 
    field = '123'
AND
    (gender IN (SELECT gender FROM @genders))

In this case, I am looking for all records where field = 123. My goal here is to say that if @genders contains records, filter by that as well.

However, if @genders is empty and we don't have any data in it, it should include all records.

How can I go about doing this? The temp tables are created based on the user selecting one or more optional pieces of criteria from the UI. If they choose a gender for example, I put their selections into a temp table and then I need to search records that meet that criteria. However, if they don't select a gender, I want to include all records, regardless of what the main record has for the gender field.

Upvotes: 0

Views: 3444

Answers (5)

PPJN
PPJN

Reputation: 352

If your query doesn't get too complicated, I'd recommend an if statement. Once you find yourself continually adding if else statement in there, I'd recommend looking into dynamic SQL.

IF EXISTS(SELECT gender FROM @genders)
BEGIN
  SELECT * FROM TABLE
  WHERE field = '123'
  and gender IN (SELECT distinct gender FROM @genders)
END
ELSE
BEGIN
  SELECT * FROM TABLE WHERE field = '123'
END

Upvotes: 0

paparazzo
paparazzo

Reputation: 45096

You are going to think this is odd but it is efficient 

SELECT t.* 
FROM TABLE t
JOIN GENDERS g 
  on g.gender = t.gender 
 and t.field = '123'
union all 
SELECT t.* 
FROM TABLE t
where not exists (select 1 from genders)

Upvotes: 1

Eric Brandt
Eric Brandt

Reputation: 8101

Maybe I'm under-thinking it, but isn't it just this?

SELECT 
    *
FROM 
    TABLE AS t
LEFT JOIN
    @genders AS g
        ON
            g.gender = t.gender
WHERE 
    field = '123'
    AND
    (g.gender = t.gender OR g.gender IS NULL);

Upvotes: 0

Elham Kohestani
Elham Kohestani

Reputation: 3162

You can use IF condition:

IF EXISTS(SELECT gender FROM @genders)
BEGIN
  SELECT * FROM TABLE
  WHERE field = '123'
  AND
  (gender IN (SELECT gender FROM @genders))
END
ELSE
BEGIN
  SELECT *  FROM TABLE
END

Upvotes: 2

JBrooks
JBrooks

Reputation: 10013

SELECT 
    * 
FROM 
    TABLE
WHERE 
    field = '123'
AND ((SELECT count(1) FROM @genders) = 0 OR
    (gender IN (SELECT gender FROM @genders)))

Upvotes: 4

Related Questions