SQL - where statement conditions

Question

Below is my query, I am fairly new to sql so my understanding is not the best. I want my query results to only give me the names I specify within my 'where' statement. Below in my query there are 4 activity names, 1 team name, and 1 date range ( 3 different colums, the 4 activities are found in the column 'name'). It has to match all these conditions.

My query works up until the second name that I specify within my 'where' statement.

Query:

SELECT *

FROM databse1.dbo.table1

where team_name = 'CTM Tier 2'
        and date >= '2018-02-01' and date <= '2018-02-28'
        and name = 'Post Vet 460' 
        and name = 'Post Vet CA Complex'
        and name = 'Post Vet CA Standard'
        and name = 'Post Vet CAA'

There query runs fine up until (and name = 'Post Vet 460'), but if I run the query as a whole it gives me blank results. Am I supposed to use a case when statement in this situation, all suggestions and tips are greatly appreciated thanks!

It's generally a bad idea to use `case` _expressions_ in `where` (and `on`) clauses. Use `and`/`or` constructions instead. — jarlh, Mar 21 '18 at 19:59

score 3 · Answer 1 · answered Mar 21 '18 at 19:52

3

it can't be all those at once, so use the in operator

and name in ('Post Vet 460', 'Post Vet CA Complex', 'Post Vet CA Standard', 'Post Vet CAA')

answered Mar 21 '18 at 19:52

yoyoyoyo123

2,362
2
22
36

score 1 · Answer 2 · answered Mar 21 '18 at 19:52

I think you want IN:

select t1.*
from databse1.dbo.table1 t1
where team_name = 'CTM Tier 2' and
      date >= '2018-02-01' and date <= '2018-02-28' and
      name in ('Post Vet 460', 'Post Vet CA Complex', 'Post Vet CA Standard', 'Post Vet CAA');

Your query returns nothing, because name cannot have different values on the same row.

You might also want like, depending on what your data looks like:

select t1.*
from databse1.dbo.table1 t1
where team_name = 'CTM Tier 2' and
      date >= '2018-02-01' and date <= '2018-02-28' and
      name like 'Post Vet %'

score 0 · Answer 3 · answered Mar 21 '18 at 19:56

Now try this query ! you need to find any row having either one of these name .so need to use OR condition .

SELECT *

FROM databse1.dbo.table1

where team_name = 'CTM Tier 2'
        and date >= '2018-02-01' and date <= '2018-02-28'
        and 
        (   name = 'Post Vet 460' 
        or  name = 'Post Vet CA Complex'
        or  name = 'Post Vet CA Standard'
        or  name = 'Post Vet CAA'
        )

score 0 · Answer 4 · answered Mar 21 '18 at 20:05

The problem you are facing is because you are using the AND operator.

You should use the OR operator, instead.

Here's the correct implementation:

WHERE team_name = 'CTM Tier 2'
        AND date >= '2018-02-01' AND date <= '2018-02-28'
        AND name = 'Post Vet 460' 
        OR name = 'Post Vet CA Complex'
        OR name = 'Post Vet CA Standard'
        OR name = 'Post Vet CAA'

As pointed out by others you can also use IN.

WHERE team_name = 'CTM Tier 2'
        AND date >= '2018-02-01' AND date <= '2018-02-28'
        AND name IN ('Post Vet 460', 'Post Vet CA Complex', 'Post Vet CA Standard', 'Post Vet CAA')

If you want to read more about IN and the differences with OR, have a look here.

SQL - where statement conditions

4 Answers4