0

I have this query that I'm trying to work with, and ultimately I'm trying to get it to return results as soon as it matches one of my status WHERE requirements....but that's not even my question. My question is that my query is returning a value that is TOTALLY out of bounds of my WHERE requirements, and it's just perplexing me as to why.

I have a table named leads. It has about ~30,000 rows in the table, but the specific dataset I want to be targeting is here:

+---------+--------+---------+
| lead_id | status | owner   |
+---------+--------+---------+
|    3653 |     18 | 6585993 |
|    3984 |      3 | 6585993 |
|    4100 |     18 | 6585993 |
|    8916 |     21 | 6585993 |
|   20329 |      1 | 6585993 |
+---------+--------+---------+

Lead ID is the primary key, it's int(11) and AUTO_INCREMENTing, status is a FOREIGN KEY, and owner is a FOREIGN KEY as well.

Here is the query I'm working with:

SELECT lead_id, status, owner FROM leads
    WHERE owner = 6585993 AND (`status` = 1) OR (`status` = 21) OR (`status` = 3)
    LIMIT 1;

My ultimate goal with this query is I want to have it cascade through my last three WHERE requirements (ie, first check the owner requirement, then - if there exists no lead with a status of "1", then check for leads with a status of "21" and if no lead exists with a status of "1" or "21", then check for leads with a status of "3", and if no leads exist with any of those three statuses who also have the correct owner requirement, return none.

With how the query is written above, it returns the what I expect:

+---------+--------+---------+
| lead_id | status | owner   |
+---------+--------+---------+
|   20329 |      1 | 6585993 |
+---------+--------+---------+

Here's the problem...when I change my status cascade to look like this (change it so that the status of "21" is to be selected first):

SELECT lead_id, status, owner FROM leads WHERE owner = 6585993 AND (`status` = 21) OR (`status` = 1) OR (`status` = 3) LIMIT 1;

It returns this:

+---------+--------+---------+
| lead_id | status | owner   |
+---------+--------+---------+
|    9435 |      1 | 1083618 |
+---------+--------+---------+

And herein lies my ultimate question...

Why does the above query return a response with a completely wrong owner?

  • Have you tried to set an order? (ORDER CASE in this case) – McNets Jul 13 at 15:28
2

First and foremost:

Change your where clause and put all OR's inside a single parenthesis.

SELECT 
    lead_id, status, owner 
FROM 
    leads 
WHERE 
    owner = 6585993 
    AND ((`status` = 1) OR (`status` = 21) OR (`status` = 3))
ORDER BY
    CASE `status` 
        WHEN 1 THEN 0 
        WHEN 21 THEN 1
        WHEN 3 THEN 2
    END
LIMIT 1
;

Then, if you prefer and order, set and ORDER BY clause using a case to assign the new values, 1 = First (0), 21 = Second (1) and 3 = Last (2).

lead_id | status |   owner
------: | -----: | ------:
  20329 |      1 | 6585993

db<>fiddle here

| improve this answer | |
  • This is exactly what I was looking for!!! I still don't know why it was matching to a completely different owner...but I don't really care about that, now that I found the answer I needed. – Adam McGurk Jul 13 at 15:57
  • There is no expected order until you set up an ORDER. – McNets Jul 13 at 15:58
  • Even shorter: status IN (1, 21, 3) – Rick James Jul 14 at 5:10
1

You have two possibility and you have to set an order when you try to LIMIT

So add to the both query below an ORDER BY, to get the right id

SELECT 
    lead_id, status, owner 
FROM leads 
WHERE owner = 6585993 
    AND ((`status` = 1) OR (`status` = 21) OR (`status` = 3)) 
ORDER BY FIND_IN_SET(status,1,21,3)
LIMIT 1;

Or you use

SELECT 
    lead_id, status, owner 
FROM leads 
WHERE owner = 6585993 
    AND `status` IN ( 1, 21, 3) 
ORDER BY FIELD(status,1,21,3)
LIMIT 1;
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.