MySQL Replace non-existent records with Not Found while using WHERE IN

Question

How do I replace non-existent records with not found in the following query. Right now I'm just getting the records that are found.

SELECT  CASE WHEN `Name` IS NOT NULL THEN `Name` ELSE 'Not Found' END AS Name
FROM    `studentDetails`
WHERE   `Transaction_ID` IN('496018490c1d5d60beb5', '77888f084c8a0e7578f5')

My input is

'496018490c1d5d60beb5',
'77888f084c8a0e7578f5'

What I'm getting is

Vinod Tonde

My desired output is

Not Found
Vinod Tonde

The Database looks like

Add your input, the output you're getting and the desired output, as well formatted text — Stefano Zanini, Apr 27 '17 at 09:00
Your database screenshot is not showing any of the `transaction_id` you are filtering in your query. — Stefano Zanini, Apr 27 '17 at 09:57
The problem is that you're assuming we already know everything about your scenario. Guess what, we DON'T. If you want an answer that's not based on guessing, please provide informations about everything we should know. — Stefano Zanini, Apr 27 '17 at 09:58

score 1 · Answer 1 · edited May 23 '17 at 12:02

1

I think what you want is this:

SELECT  Transaction_ID, CASE WHEN count(*)>0 THEN `Name` ELSE 'Not Found' END AS Name
FROM    `studentDetails`
WHERE   `Transaction_ID` IN('496018490c1d5d60beb5', 'b6836a07a3c49af6187f')
group by Transaction_ID

OR, using this you could also try:

SELECT  IFNULL(Name, 'Not Found') AS Name
FROM (SELECT '496018490c1d5d60beb5' as col 
      union all 
      SELECT 'b6836a07a3c49af6187f') temp_table 
      LEFT JOIN studentDetails 
      ON temp_table.col=studentDetails.Transaction_ID collate utf8mb4_unicode_ci

edited May 23 '17 at 12:02

Community

1
1

answered Apr 27 '17 at 09:20

dev8080

3,950
1
12
18

Tried your query and got an error #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '`Name` ELSE 'Not Found' END AS Name FROM `studentDetails` WHERE `Transactio' at line 1 – Layers Apr 27 '17 at 09:23
It runs, but unfortunately it still only returns the records that are found – Layers Apr 27 '17 at 09:41
check the 2nd query. – dev8080 Apr 27 '17 at 10:35
#1267 - Illegal mix of collations (utf8mb4_unicode_ci,COERCIBLE) and (latin1_swedish_ci,IMPLICIT) for operation '=' – Layers Apr 27 '17 at 10:44
Don't know how that is. – dev8080 Apr 27 '17 at 10:58
Add the collate portion (2nd query) and see how it works pls. – dev8080 Apr 28 '17 at 04:38
#1253 - COLLATION 'utf8mb4_unicode_ci' is not valid for CHARACTER SET 'latin1' – Layers Apr 28 '17 at 07:07
try collate latin1_swedish_ci instead and see. – dev8080 Apr 28 '17 at 09:42

score 0 · Answer 2 · answered Apr 27 '17 at 09:05

0

Try this:

UPDATE `studentDetails` SET  `Name` = 'Not Found' WHERE (`Name` IS NULL OR `Name` = '') AND `Transaction_ID` IN('496018490c1d5d60beb5', 'b6836a07a3c49af6187f')

answered Apr 27 '17 at 09:05

Biju P Dais

131
1
12

No I don't want to Write to the database I just want to reformat the results to include "Not Found" when the record does not exist – Layers Apr 27 '17 at 09:13

Spock · Answer 3 · 2017-04-27T15:00:54.033

Create a table for the ids to search:

CREATE TABLE ids (id VARCHAR(30));

Create a procedure to split a list of ids separated by a ',' and insert them into the ids table:

CREATE PROCEDURE split_id_list(IN input VARCHAR(300))
    BEGIN
    DECLARE tot_length int;
    DECLARE sub_length int;

    my_loop: LOOP

        SET tot_length = CHAR_LENGTH(input);

        INSERT INTO ids (id) VALUES(SUBSTRING_INDEX(input, ',', 1));

        SET sub_length = CHAR_LENGTH(SUBSTRING_INDEX(input, ',', 1))+2;
        SET input = MID(input, sub_length, tot_length);
            IF input = '' THEN
                LEAVE my_loop;
            END IF;
        END LOOP my_loop;
END;

Create a procedure to generate view containing the results:

CREATE PROCEDURE idsNames(IN id_list VARCHAR(500))
    BEGIN

    DECLARE a INT;
    DECLARE cur1 CURSOR for select count(*) FROM ids;

    OPEN cur1;
    FETCH cur1 into a;

    IF a > 0
         THEN DELETE FROM ids;
    END IF;

    CLOSE cur1;

    call split_id_list(id_list);

    CREATE OR REPLACE VIEW RESULTS(r_id,r_name) AS
    (SELECT ids.id, CASE WHEN Name IS NULL then 'NotFound' ELSE Name END
    FROM studentDetails 
    RIGHT JOIN ids
    ON studentDetails.Transaction_ID = ids.id); 

END;

Once the table and the procedures are created, each time you want to execute them, just execute the procedure with the required ids and a select from the view:

CALL idsNames('496018490c1d5d60beb5,b6836a07a3c49af6187f');
SELECT r_name FROM RESULTS;

No! It's not working... still returns only the records that are found — Layers, Apr 27 '17 at 09:39
I've tried before posting, with a table created for this purpose, containing just the column Name, with correct values, null values and empty values and it works. Are you sure that some names corresponding to the range of Transaction_ID for which you filter are empty or null? — Spock, Apr 27 '17 at 09:43
Sorry, I misanderstood your question, now I have updated my answer using procedures so that each time you want to search for some ids you can call the procedure idsNames with the ids separated by a ',', such as CALL idsNames('1,2,....,10') and select from the view. — Spock, Apr 27 '17 at 15:03

Stefano Zanini · Answer 4 · 2017-04-27T10:26:31.070

0

You can add a few more alternatives to your case

SELECT  CASE
            WHEN Name IS NULL THEN 'Not Found'
            WHEN length(trim(Name)) = 0 THEN 'Not Found'
            ELSE Name
        END AS Name
FROM    studentDetails
WHERE   Transaction_ID IN('496018490c1d5d60beb5', 'b6836a07a3c49af6187f')

Edit

Since it seems that one of those two transaction_id doesn't exist in the database at all, you probably need something like this instead

select  coalesce(t2.Name, 'Not Found') as Name
from    (select '77888f084c8a0e7578f5' as trans union all select '496018490c1d5d60beb5') t1
left join
        studentDetails t2
on      t1.trans = t2.Transaction_ID

edited Apr 27 '17 at 10:26

answered Apr 27 '17 at 09:41

Stefano Zanini

5,876
2
13
33

No! It's not working... still returns only the records that are found – Layers Apr 27 '17 at 09:43
1

Then add detailed informations about your input table! We're all guessing what's in `Name` here – Stefano Zanini Apr 27 '17 at 09:46
I edited my answer. From what you said, I think this is what you need – Stefano Zanini Apr 27 '17 at 10:12
No it still just returns only the name that it can find – Layers Apr 27 '17 at 10:14
Correct, it was bugged. Now will work, you can see it in action [here](http://rextester.com/LTSIF79313) – Stefano Zanini Apr 27 '17 at 10:27
it errors #1267 - Illegal mix of collations (utf8mb4_unicode_ci,COERCIBLE) and (latin1_swedish_ci,IMPLICIT) for operation '=' – Layers Apr 27 '17 at 10:46

MySQL Replace non-existent records with Not Found while using WHERE IN

4 Answers4