Reordering the clauses

Take a look at these two queries.
first:

SELECT IF(u.lastname IS NULL,b.comp_name,CONCAT(u.name, " ", u.lastname)) AS name,u.user_ID,t.user_type,b.sched_entered 
        FROM users u 
        join business_users b on u.user_id=b.crid 
        join buz_usertype t on b.bus_user_type=t.type_id  
        WHERE u.active=1 AND b.sched_entered=1     
        AND u.lastname LIKE CONCAT("%",'k', "%") OR b.comp_name LIKE CONCAT("%",'k', "%");

Second:

SELECT  IF(u.lastname IS NULL,b.comp_name,CONCAT(u.name, " ", u.lastname)) AS name,u.user_ID,t.user_type,b.sched_entered 
   FROM users u 
   join business_users b on u.user_id=b.crid 
   join buz_usertype t on b.bus_user_type=t.type_id  
   WHERE u.lastname  LIKE CONCAT("%",'k', "%") OR b.comp_name LIKE CONCAT("%",'k', "%") AND b.sched_entered=1 AND u.active=1;

Take a closer look towards the end and the where clause…in the first query it says
WHERE u.active=1 AND b.sched_entered=1
and in the second says
WHERE u.lastname LIKE CONCAT(“%”,‘k’, “%”) OR b.comp_name LIKE CONCAT(“%”,‘k’, “%”)

I just trying to use different syntax to accomplish the same thing.
And the question is if these two different syn-taxes accomplish the same thing.

Cause testing them I have a different result.
I thought they would bring the same result but they do not.

Can someone explain it to me?
It it reasonable to have different result?

Because as soon as you add an or like that, all other conditions are essentially ignored and as soon as one portion is true, it’s all true. If you want to restrict it to use the other criteria PLUS either of the like conditions, you need to add parenthesis.

SELECT IF(u.lastname IS NULL,b.comp_name,CONCAT(u.name, " ", u.lastname)) AS name
     , u.user_ID
     , t.user_type
     , b.sched_entered 
  FROM users u 
  JOIN business_users b ON u.user_id=b.crid 
  JOIN buz_usertype t ON b.bus_user_type=t.type_id  
 WHERE u.active = 1 
   AND b.sched_entered = 1     
   AND (u.lastname LIKE CONCAT("%",'k', "%") OR 
        b.comp_name LIKE CONCAT("%",'k', "%"));
1 Like

or, put another way… ANDs take precedence over ORs, so

WHERE  x  OR  y  AND  z

is evaluated as

WHERE  x  OR ( y  AND  z )

i.e. it is not evaluated as

WHERE ( x  OR  y )  AND  z

so if you want the former, parentheses are optional, but if you want the latter, you have to code the parentheses

by the way, nice sql formatting, dave :wink:

p.s. guys, you can simplify this –

LIKE CONCAT("%",'k', "%")

to this –

LIKE '%k%'
1 Like

I just assumed that was pseudo php/mysql hybird stuff…otherwise I would have just changed it to that :smiley:

1 Like

[quote=“DaveMaxwell, post:2, topic:234768, full:true”]
Because as soon as you add an or like that…[/quote]

Do you have a typo above?
Is this below what you want to say?

Because as soon as you add and or like that…

It is an important detail.

Nope. It’s the OR that causes you to easily lose control unless you have the order down correctly (or you use parenthesis)

I am just beginning to understand what is wrong with my query(after reading many times the posts).

The wrong query is this…once again.

SELECT  IF(u.lastname IS NULL,b.comp_name,CONCAT(u.name, " ", u.lastname)) AS name,u.user_ID,t.user_type,b.sched_entered 
        FROM users u 
        join business_users b on u.user_id=b.crid 
        join buz_usertype t on b.bus_user_type=t.type_id  
        WHERE u.lastname  LIKE CONCAT("%",'k', "%") OR b.comp_name LIKE CONCAT("%",'k', "%") 
        AND b.sched_entered=1 
        AND u.active=1;

This query returns a row while it should’nt(the user whose last name starts with k has b.sched_entered of 1 but u.active 0).

The reason this happens(as I have come to understand) cause there is a lastname from k and b.sched_entered=1(in the db table)…since these two are both true the last clause is simply ignored…despite u.active has a value of 0…in the db table.

Is the above logic correct?

No. This query will return a row if EITHER or these two criteria are correct

  • lastname contain a K
  • comp_name contain a K AND sched_entered = 1 AND active = 1

It would be the same as if you ran these two queries…

SELECT  IF(u.lastname IS NULL,b.comp_name,CONCAT(u.name, " ", u.lastname)) AS name
      , u.user_ID
      , t.user_type
      , b.sched_entered 
   FROM users u 
   JOIN business_users b ON u.user_id = b.crid 
   JOIN buz_usertype t ON b.bus_user_type = t.type_id  
  WHERE u.lastname  LIKE CONCAT("%",'k', "%")

and

SELECT  IF(u.lastname IS NULL,b.comp_name,CONCAT(u.name, " ", u.lastname)) AS name
      , u.user_ID
      , t.user_type
      , b.sched_entered 
   FROM users u 
   JOIN business_users b ON u.user_id = b.crid 
   JOIN buz_usertype t ON b.bus_user_type = t.type_id  
  WHERE b.comp_name LIKE CONCAT("%",'k', "%") 
    AND b.sched_entered = 1 
    AND u.active = 1;

If you want it to show only the rows where they are active and have a schedule entered (I’m guessing that’s what sched_entered means) AND either the company name or the last name has a K in it, you MUST use parenthesis

SELECT  IF(u.lastname IS NULL,b.comp_name,CONCAT(u.name, " ", u.lastname)) AS name
      , u.user_ID
      , t.user_type
      , b.sched_entered 
   FROM users u 
   JOIN business_users b ON u.user_id = b.crid 
   JOIN buz_usertype t ON b.bus_user_type = t.type_id  
  WHERE (b.comp_name LIKE '%k%'  OR u.lastname LIKE '%k%')
    AND b.sched_entered = 1 
    AND u.active = 1;

In THIS query, this will only return rows IF ALL of these are true

  • either comp_name OR lastname contain a K
  • sched_entered = 1
  • active = 1
1 Like

You should try to avoid using a wildcard in before and after if possible, as it makes the query quite slow.

In addition, assuming that the requirement is that either u.lastname or b.comp_name contain a k and also the two other clauses need to match. Then you are much better of setting this up using UNION, where one select check for matches to u.lastname and the other check for matches to b.comp_name.

Now I got it…thanks.

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.