SQL Or condition not working

ukerandi
ukerandi used Ask the Experts™
on
Hi Experts,
 Please my sql query.
I need to get all the hire and inter data only but results is wrong any idea for this
issue
SELECT typeName,typeName,stageName,typeID,[stageID] FROM tblcandidate 
  WHERE candidateID='06f94c06364501'
  AND (typeName Like 'hire%' OR typeName Like 'inter%'
 OR  stageName Like 'Hire%'   OR  [stageID] Like 'hire%'
  OR [typeID] Like 'hire%'
  OR [stageName] Like 'inter%' OR [typeName] Like 'inter%'  OR  [stageID] Like 'inter%'
   OR  [typeID] Like 'inter%')

Open in new window


see Results

typeName      typeName      stageName      typeID      stageID
Sourced               Sourced      Internal portal sourced        1563369937876
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
When you are combining AND and OR conditions without braces, OR will bring all records out.
Kindly post the results of the query above and the expected result set to identify the issue..

Author

Commented:
@Raja Jegan R
see Results

typeName      typeName      stageName      typeID      stageID
Sourced               Sourced      Internal portal sourced        1563369937876

Expected Results : No results
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
Kindly double check for any blank spaces or special characters in the data by running the below query..
Below should return your result set expected and if not, then the StageName column contains some special characters.
Also confirm whether StageName column data is case sensitive or not..
  SELECT typeName,typeName,stageName,typeID,[stageID] FROM tblcandidate 
  WHERE candidateID='06f94c06364501'
  AND  [stageName] Like 'inter%'

Open in new window

  SELECT typeName,typeName,stageName,typeID,[stageID] FROM tblcandidate 
  WHERE candidateID='06f94c06364501'
  AND  [stageName] Like 'Inter%' -- To Check column case sensitiveness 

Open in new window

Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

John TsioumprisSoftware & Systems Engineer

Commented:
I think this is a case where Normalization shines :
You need a table that stores the different states
Sourced
Internal
Portal
........

Open in new window

You could then filter them without worrying for special chars or what soever
Miguel OzSenior Software Engineer
Top Expert 2009

Commented:
To avoid any case sensitive issue you could use the lower function:
SELECT typeName,typeName,stageName,typeID,[stageID] FROM tblcandidate 
  WHERE candidateID='06f94c06364501'
  AND ((lower(typeName) like 'hire%' OR lower(typeID) like 'hire%' OR lower(stageName) like 'hire%' OR lower([stageID]) like 'hire%')
    OR (lower(typeName) Like 'inter%' OR lower(typeID) like 'inter%' OR lower(stageName) like 'inter%' OR lower([stageID]) like 'inter%'))

Open in new window

Of course there are better ways to normalize your data, for starters I will create all these types under id, name description table and just use the id to identify your hire and inter data.
Mark EdwardsChief Technology Officer

Commented:
You are getting the proper results for your criteria:
For criteria:  
OR [stageName] Like 'inter%'
and the record has:
stageName
Internal
Why do you think this is incorrect?  You're looking for a record with [stageName] starting with "Inter" and "Internal" meets that criteria.

Think again and get back to us....
SQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018
Commented:
>> Why do you think this is incorrect?  You're looking for a record with [stageName] starting with "Inter" and "Internal" meets that criteria.

Nice catch Mark Edwards, I got it the other way and hence requested ukerandi to check for case sensitiveness..

Author

Commented:
I will check and updated soon
Mark EdwardsChief Technology Officer

Commented:
Are you wanting your comparisons to be case-sensitive, where "inter" does NOT equal "Inter"?
This is called a "binary comparison".  Is that what you are wanting?

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial