SQL WHERE statement not working 100%

Question

I want to return values ​​where World = yes or where you can see = yes and State = Florida , but this does not return all other values ​​in which World yes

select * from Table1 where (visible = 'yes' and State = 'Florida') or Worldwide= 'yes' order by ID DESC 

Edit: My BAD

Sorry guys / girls, this expression really works! In my application, I selected TOP 8 *, so it did not return all entries! When I took the TOP 8, it worked! My bad!

Answer 1

Using the following script, the query displays the 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' 

Choose

 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 

Answer 2

Try the following:

 where State = 'Florida' AND (visible = 'yes' or Worldwide= 'yes') 

Another option that covers all possible cases with syntax:

 where UPPER([State]) LIKE '%FLORIDA%' AND ((UPPER(visible) LIKE '%YES%') OR (UPPER(Worldwide) LIKE '%YES%')) 

Answer 3

You may have some zeros that may cause some problems.

 select * from Table1 where (IsNull(visible,'') = 'yes' and IsNull(State,'') = 'Florida') or IsNull(Worldwide,'')= 'yes' order by ID DESC 

Also check that sorting is not case sensitive. Calling can be set at the server, database or column level, so you will need to check the following:

Server Sort

 SELECT SERVERPROPERTY('COLLATION') 

Database mapping

 SELECT DATABASEPROPERTYEX('DATABASENAME', 'Collation') SQLCollation; 

Column collation

 Select table_name, column_name, collation_name From information_schema.columns Where table_name = @table_name 

Answer 4

You have up to 4 questions

• case sensitivity
• of space
• Null values
• unexpected data

Options you can combine

 --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