CODEWITHSUNDEEP
sql
Steve W
Steve W

Reputation: 1128

Using AND command in SQL Server not giving expected result

I am trying to write some code to identify whether 2 values in 1 table exist in another table before executing some other code. I find these:

select count(1)
from Team
where TeamName IN (select Data FROM CSVTest_Match WHERE Header = 'Home Team');

select count(1)
from Team
where TeamName IN (select Data FROM CSVTest_Match WHERE Header = 'Away Team');

both return the value 1 as expected, when the values are present in table Team column TeamName. However, this:

select count(1)
from Team
where TeamName IN (select Data FROM CSVTest_Match WHERE Header = 'Home Team')
AND TeamName IN (select Data FROM CSVTest_Match WHERE Header = 'Away Team');

returns 0 when (as far as I can see) it should combine the 1st two queries and return 1. Can anybody tell me what I am doing wrong?

Upvotes: 0

Views: 48

Answers (2)

tibtof
tibtof

Reputation: 7957

I think it's more readable this way:

select count(1)
from Team
where TeamName IN (select Data FROM CSVTest_Match WHERE Header in ('Home Team', 'Away Team'))

To return 1 one way would be to decode the result:

select case when count(1) = 0 then 0 else 1 end
from Team
where TeamName IN (select Data FROM CSVTest_Match WHERE Header in ('Home Team', 'Away Team'))

Upvotes: 4

Ocaso Protal
Ocaso Protal

Reputation: 20247

What your current query says is: Count all rows where TeamName is Home AND Away team. Replace ANDwith OR: One team can either be home or away team: 

select count(1)
from Team
where TeamName IN (select Data FROM CSVTest_Match WHERE Header = 'Home Team')
OR TeamName IN (select Data FROM CSVTest_Match WHERE Header = 'Away Team');

EDIT: And a quick and dirty way to just return 1:

select CASE WHEN count(1) > 0 THEN 1 ELSE 0 END
from Team
where TeamName IN (select Data FROM CSVTest_Match WHERE Header = 'Home Team')
OR TeamName IN (select Data FROM CSVTest_Match WHERE Header = 'Away Team');

Upvotes: 2

Related Questions