SQL WHERE AND & OR Clauses

I am attempting to execute this SQL statement, and while it does indeed execute, it does not return the expected result. 开发者_Python百科Below is the WHERE clause:

WHERE SubnetID.ID = '2' 
  AND Subnets.DomainName LIKE '%tns%' 
  OR Subnets.ServerID LIKE '%tns%' 
  OR CONCAT(SubnetID.IP_Prefix,'.',Subnets.LastOctet) LIKE '%tns%' 
  OR Servers.Name LIKE '%tns%' 
  OR Subnets.Comment LIKE '%tns%'

What happens is the WHERE SubnetID.ID = '2' line seems to be completely disregarded as I return rows with the SubnetID.ID set to values other than '2'. It is processing LIKE clauses, it is just not filtering out the SubnetID.ID values.

I am thinking this has to do with using AND/OR/LIKE together, but I am not sure.

This should work

WHERE SubnetID.ID = '2' AND 
(
  Subnets.DomainName LIKE '%tns%' OR
  Subnets.ServerID LIKE '%tns%' OR
  CONCAT(SubnetID.IP_Prefix,'.',Subnets.LastOctet) LIKE '%tns%' OR
  Servers.Name LIKE '%tns%' OR
  Subnets.Comment LIKE '%tns%'
)

Your query was executed this way

WHERE (
  SubnetID.ID = '2' AND 
  Subnets.DomainName LIKE '%tns%'
) OR
(Subnets.ServerID LIKE '%tns%') OR
(CONCAT(SubnetID.IP_Prefix,'.',Subnets.LastOctet) LIKE '%tns%') OR
(Servers.Name LIKE '%tns%') OR
(Subnets.Comment LIKE '%tns%')

Try This:

WHERE SubnetID.ID = '2' AND 
( Subnets.DomainName LIKE '%tns%' 
  OR Subnets.ServerID LIKE '%tns%' 
  OR CONCAT(SubnetID.IP_Prefix,'.',Subnets.LastOctet) LIKE '%tns%' 
  OR Servers.Name LIKE '%tns%' 
  OR Subnets.Comment LIKE '%tns%
)

The operator precedence is most likely what is causing the problem. Here is the link from microsoft if you are using one of their DBMS products.