I cant figure out why my function gets error code 1242

All we need is an easy explanation of the problem, so here it is.

My function gives error code 1242 on what I believe is this line and I cant figure out why

else select group_concat(concat(last_name,':',TIMESTAMPDIFF(YEAR, birthday, CURDATE()),':',major)separator ',') into result from dreamhome.Students
    where sid in (select sid from dreamhome.Students_Courses where cid IN (select cid from dreamhome.Courses where name like concat('%',courseName,'%')) group by sid);

below is the whole function

DELIMITER //
    CREATE FUNCTION findName (courseName varchar(30))
    RETURNS varchar(170)
    BEGIN
       DECLARE result varchar(170);
       
    if IFNULL(courseName,'') = '' then set result = 'Please input a valid name';
    elseif (select name from dreamhome.Courses where name like concat('%',courseName,'%')) is null then set result = concat('no course found for name: ', courseName);
    else select group_concat(concat(last_name,':',TIMESTAMPDIFF(YEAR, birthday, CURDATE()),':',major)separator ',') into result from dreamhome.Students
    where sid in (select sid from dreamhome.Students_Courses where cid IN (select cid from dreamhome.Courses where name like concat('%',courseName,'%')) group by sid);
    END IF;       
    RETURN result;
    END //
    DELIMITER ;

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

The query gives an error, when you have more than one course that fits the name.

so you get multiple names back and that you can’t compare to NULL

What you can di is USE LIMIT so that you receive only one name

OR You can use NOT EXISTS like in the second function

CREATE tABLe Courses (cid int,name varchar(50))
INSERT INTO Courses VALUEs (1,'a'),(2,'ab')
CREATE TABLe Students (sid int,last_name varchar(50),birthday date,major varchar(50))
INSERT INTO Students VALUEs (1,'testa','2020-01-01','testC'),(2,'testb','2020-02-01','testD')
CREATE TABLe Students_Courses (sid int,cid int)
INSERT INTO Students_Courses VALUES(1,1),(2,2)
    CREATE FUNCTION findName (courseName varchar(30))
    RETURNS varchar(170)
    BEGIN
       DECLARE result varchar(170);
       
    if IFNULL(courseName,'') = '' then 
        set result = 'Please input a valid name';
    elseif 
       (select name from Courses where name like concat('%',courseName,'%') LIMIT 1) is null 
         then set result = concat('no course found for name: ', courseName);
    else 
       select group_concat(concat(last_name,':',TIMESTAMPDIFF(YEAR, birthday, CURDATE()),':',major)separator ',') into result from Students
       where sid in (select sid from Students_Courses where cid IN (select cid from Courses where name like concat('%',courseName,'%')) group by sid);
    END IF;       
    RETURN result;
    END 
SELECT findName('a');
| findName('a')         |
| :-------------------------- |
| testa:1:testC,testb:1:testD |
    CREATE FUNCTION findName1 (courseName varchar(30))
    RETURNS varchar(170)
    BEGIN
       DECLARE result varchar(170);
       
    if IFNULL(courseName,'') = '' then 
        set result = 'Please input a valid name';
    elseif 
       (NOT EXISTS(select 1 from Courses where name like concat('%',courseName,'%'))) 
         then set result = concat('no course found for name: ', courseName);
    else 
       select group_concat(concat(last_name,':',TIMESTAMPDIFF(YEAR, birthday, CURDATE()),':',major)separator ',') into result from Students
       where sid in (select sid from Students_Courses where cid IN (select cid from Courses where name like concat('%',courseName,'%')) group by sid);
    END IF;       
    RETURN result;
    END 
SELECT findName1('a');
| findName1('a')        |
| :-------------------------- |
| testa:1:testC,testb:1:testD |
SELECT findName1('c');
| findName1('c')        |
| :-------------------------- |
| no course found for name: c |

db<>fiddle here

Method 2

You have multiple rows coming back in your subqueries, which is why you’re getting the error. If the values are always the same, you may be able to use a SELECT DISTINCT in the subquery. Otherwise, a simple way to resolve the issue is to use a LIMIT 1, ideally with a sort so the most relevant results are returned more often.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply