CODEWITHSUNDEEP
sqlite
cypronmaya
cypronmaya

Reputation: 520

SQLite query for may not be in condition

I've a table with following information.

AreaDescription     AD
------------------
Processing1         BB
Geology         BC
pilot           BB

Need dynamic way to find if there's no match in AD for a given set ('BC','G','S') it returns all the records, otherwise just return the ones which matches in the given set ('BC','G','S')

select * from tblArea where AreaDescription like '%o%' and (AD in ('BC','G','S') or 1=1)

I was trying the above SQL, where it contains

(AD in ('BC','G','S') or 1=1)

expressing if AD has value in ('BC','G','S') then return those or else return everything as per 1=1 condition.

But this doesn't work as I thought it works.

Here I get all rows not only which contains only 'BC', so I guess the OR condition is not correct way?

Is there a way I can do this? using SWITCH ?

Upvotes: 0

Views: 33

Answers (1)

CL.
CL.

Reputation: 180060

Just combine two queries, and use EXISTS in the second one to check if the first did match any records:

SELECT *
FROM tblArea
WHERE AreaDescription LIKE '%o%'
  AND AD IN ('BC', 'G', 'S')
UNION ALL
SELECT *
FROM tblArea
WHERE AreaDescription LIKE '%o%'
  AND NOT EXISTS (SELECT 1
                  FROM tblArea
                  WHERE AreaDescription LIKE '%o%'
                    AND AD IN ('BC', 'G', 'S'))

Upvotes: 1

Related Questions