SQL WHERE statement not working 100% [closed]

It's difficult to tell what is being asked here. This question is ambiguous, vague, incomplete, overly broad, or rhetorical and cannot be reasonably answered in its开发者_运维百科 current form. For help clarifying this question so that it can be reopened, visit the help center. Closed 12 years ago.

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