SQL WHERE statement not working 100% [closed]
I want to return values where Worldwide = yes or where visible = yes and in State = Florida but this is not returning all the other values where the Worldwide is yes
select * from Table1
where (visible = 'yes' and State = 'Florida') or Worldwide= 'yes'
order by ID DESC
Edit: My BAD
Sorry guys/girls, this statement does work! I had Select TOP 8 * in my statement that is why it did not return all the records! When I took the TOP 8 out it worked! My bad!
Using following script, the query outputs what is expected.
Create data
DECLARE @Table1 TABLE (
ID INTEGER IDENTITY(1, 1)
, State VARCHAR(32)
, Visible VARCHAR(32)
, WorldWide VARCHAR(32)
)
INSERT INTO @Table1
SELECT 'Florida', 'Yes', 'Yes'
UNION ALL SELECT 'Florida', 'Yes', 'No'
UNION ALL SELECT 'Florida', 'No', 'Yes'
UNION ALL SELECT 'Florida', 'No', 'No'
UNION ALL SELECT 'Other State', 'Yes', 'Yes'
UNION ALL SELECT 'Other State', 'Yes', 'No'
UNION ALL SELECT 'Other State', 'No', 'Yes'
UNION ALL SELECT 'Other State', 'No', 'No'
Select
SELECT *
FROM @Table1
WHERE (Visible = 'Yes' AND State = 'Florida') OR WorldWide = 'Yes'
Output
ID State Visible WorldWide
1 Florida Yes Yes
2 Florida Yes No
3 Florida No Yes
5 Other State Yes Yes
7 Other State No Yes
Try this:
where State = 'Florida' AND (visible = 'yes' or Worldwide= 'yes')
The other variant which covers all possible cases with syntax:
where UPPER([State]) LIKE '%FLORIDA%' AND
((UPPER(visible) LIKE '%YES%') OR (UPPER(Worldwide) LIKE '%YES%'))
Maybe you have some nulls in there which may cause you some trouble.
select * from Table1
where (IsNull(visible,'') = 'yes' and IsNull(State,'') = 'Florida')
or IsNull(Worldwide,'')= 'yes'
order by ID DESC
Also check that the collation isn't case sensitive. Collation can be set at Server, Database or Column level so you will need to check the following:
Server Collation
SELECT SERVERPROPERTY('COLLATION')
Database Collation
SELECT DATABASEPROPERTYEX('DATABASENAME', 'Collation') SQLCollation;
Column Collation
Select table_name, column_name, collation_name
From information_schema.columns
Where table_name = @table_name
You have up to 4 issues
- case sensitivity
- spaces
- NULLs
- unexpected data
Variations which can be combined
--case
(LOWER(visible) = 'yes' and LOWER(State) = 'Florida') or LOWER(Worldwide) = 'yes'
--spaces
(RTRIM(LTRIM(visible)) = 'yes' and RTRIM(LTRIM(State)) = 'Florida') or RTRIM(LTRIM(Worldwide)) = 'yes'
--nulls
(visible = 'yes' and State = 'Florida') or ISNULL(Worldwide, 'yes') = 'yes'
--unexpected data: need samples
精彩评论