How can I determine which condition on WHERE clause fails?

This always returns one row. 1 for matched if there is a match otherwise 0. It also shows if the userID or the token exists in the cookies table. Using this method you can determine why the where clause failed.

SELECT (  select count(*) > 0 matched from cookies where user_id = :id  and token   = :token ) matched, ( select count(*) > 0 userIdExists from cookies where user_id = :id ) userIdExists, ( select count(*) > 0 tokenExists from cookies where token = :token ) tokenExists

PREFACE: This problem, notwithstanding my dislike of MySQL, was a rather tough puzzle to solve. No use of an FULL OUTER JOIN made it even harder. Even the "solution" I initially gave was insufficient for the task.

OUTER JOINS behave in a specific pattern, and it was important to think about each inner query as independent from the outer opposite side as the explicit direction.

SELECT A.Col_A, B.Col_BTableA ALEFT OUTER JOIN TableB B ON B.ID = A.ID

This query only produces results if TableA has something to match TableB with. Because every nested query is logically the same as the above, even adding a dummy table only left me with just the result from the dummy table and me feeling like a...dummy.

While some smart users have tried UNION/UNION ALL to solve this, that answer is unreliable and actually failed when I attempted to use the same table twice.

• The Solution: FOR REAL!!

The trick is to guarantee the results will always return.

SELECT C.user_id     , B.tokenFROM (SELECT NULL AS C) ALEFT OUTER JOIN (SELECT token                 FROM  Example                 WHERE token = @token) AS B ON 1 = 1 LEFT OUTER JOIN (SELECT user_id                 FROM Example                 WHERE user_id = @user_id) AS C ON 1 = 1

• We use a DUMMY table to ensure we always get a row...even if it is empty.
• Notice On 1 = 1. That guarantees that the results from both sides will return and since we made sure those tables only retrieved the exact information we wanted, bingo! we get out beautiful solution that will work regardless of whether one or both sides are NULL.

A RECHECK ON THE ORIGINAL PROCEDURE

TABLE DECLARATIONS:

CREATE TABLE sys.EXAMPLE (ID INT auto_increment PRIMARY KEY NOT NULL                    , user_id INT NULL                    , token NVARCHAR(100) NULL                    , `expire` INT NULL ); INSERT INTO sys.EXAMPLE (user_Id, token, `expire`)VALUES  (32423, N'dki3j4rf9u3e40...', 1467586386)      , (65734, N'erhj5473fv34gv...', 1467586521)      , (21432, N'8u34ijf34t43gf...', 1467586640);

-- My MySQL Workbench bugs out at the beginning, for some reason the variables are persisting beyond the transaction.

PROCEDURE SOLUTION

-- DROP PROCEDURE sys.MyExampleDELIMITER $$CREATE PROCEDURE sys.MyExample(IN user_ID INT, IN token NVARCHAR(255) )   BEGINSELECT B.token, C.user_id INTO @token_chk, @user_chkFROM (SELECT NULL AS C) ALEFT OUTER JOIN (SELECT token                FROM  sys.Example                WHERE token = @token                 LIMIT 1) AS B ON 1 = 1 LEFT OUTER JOIN (SELECT user_id                 FROM sys.Example                 WHERE user_id = @user_id                 LIMIT 1) AS C ON 1 = 1;IF @user_chk IS NOT NULL AND @token_chk IS NOT NULL-- RESULT_1: FOUND BOTH COLUMNSTHEN SELECT 'FOUND IT';    ELSE IF @user_chk IS NOT NULL AND @token_chk IS NULL-- RESULT_2: TOKEN IS MISSING         THEN SELECT 'TOKEN IS MISSING';         ELSE IF @user_chk IS NULL AND @token_chk IS NOT NULL-- RESULT_3: USER_ID IS MISSING                    THEN SELECT 'user_ID IS MISSING';                    ELSE IF @user_chk IS NULL AND @token_chk IS NULL-- RESULT_4: BOTH USER_ID AND TOKEN ARE MISSING                          THEN SELECT 'BOTH user_id AND token are missing';                          ELSE -- return message saying that an unknown error has occurred                          SELECT 'AN UNKNOWN ERROR HAS OCCURRED';                          END IF;                    END IF;            END IF;    end if;END$$DELIMITER ; 

Note that this works on T-SQL and works in MySQL before I run the PROCEDURE. For some reason, I am having a bug where the second variable in my proc loses its value during the query...even when I explicitly call the same value before the query inside the Procedure.

-- Should Return complete set    CALL sys.MyExample(21432, 'erhj5473fv34gv...');-- Should Return 'token is missing'    CALL sys.MyExample(21432, 'erhj5473fv34gv..X'); -- Should Return 'user_id is missing'    CALL sys.MyExample(2143, 'erhj5473fv34gv...'); -- Should Return 'Both user_id AND token are missing'    CALL sys.MyExample(2143, 'erhj5473fv34gv..X');  

There is no way (that I'm aware of, at least) to get this kind of information from just one query like the one you have here. Your database will simply run the query with the combined criteria you provide and conclude that those criteria either match or don't match any rows. It won't bother itself with exactly which of your criteria made it match or not match any rows.

Assuming you are using these queries as part of some server-side page logic (like a PHP file), I'd say three queries is the minimum you can do this in.

1. The query you already have, which returns a row only if there is a match between the token and the user ID you're looking up.

2. A query to return the number of rows that match the token.

3. A query to return the number of rows that match the user ID.

If query #1 returns a row, the last two are not needed, of course: option #1 is satisfied, skip along to subsequent code. But if it doesn't, you'll need both the other queries to determine which of the other four possible options reflects the state of things:

If query #3 returns more than zero rows and query #2 returns zero rows, the user ID exists but the token doesn't—option #2 satisfied.

If query #2 returns more than zero rows and query #3 returns zero rows, the token exists but the user ID doesn't—option #3 satisfied.

If both queries return zero rows, neither token nor user ID exists—option #4 satisfied.

If both queries return more than zero rows, both token and user ID exist, but since query #1 didn't return any rows, they don't exist in the same row—option #5 satisfied.