MySQL returning value from a SELECT statement

Question

I keep getting this error: Result consisted of more than one row

I have this function:

DROP FUNCTION IF EXISTS db.GetUserIDByCourseID;
CREATE FUNCTION db.`GetUserIDByCourseID`(CourseID int) RETURNS int(11)
BEGIN
      SELECT (c.user_id + COALESCE(pi.user_id, 0) + COALESCE(p.user_id, 0))
        INTO @user_id
        FROM courses c
             LEFT JOIN users u
                ON u.user_id = c.user_id
             LEFT JOIN partners p
                ON p.partner_id = c.partner_id
             LEFT JOIN partners_individual pi
                ON pi.individual_id = c.individual_id;
             WHERE c.course_id = CourseID;

      SELECT user_type_id
        INTO @user_type_id
        FROM users
       WHERE user_id = @user_id;

      RETURN @user_type_id;
   END;

If I might be permitted an observation, your function GetUserIDByCourseID doesn't return a user_id but a user_type_id. I think this may lead to confusion for the callers. — Brian Hooper
– Brian Hooper, Commented Sep 17, 2010 at 6:31

Brian Hooper · Accepted Answer · 2010-09-17 05:55:46Z

2

When SELECTing into a variable, the result set must consist of exactly one row. In your current example, it seems likely that the first select statement will return more than one row; you can check that by executing it by hand for the values of CourseID that are giving trouble. What to do about it I couldn't say, as that depends on the details of your design.

answered Sep 17, 2010 at 5:55

Brian Hooper

22.2k25 gold badges91 silver badges146 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Aivan Monceller Over a year ago

Thank you, I missed this clause on original post [WHERE c.course_id = CourseID;]

JohnB · Accepted Answer · 2010-09-17 06:05:21Z

0

You could try SELECT TOP 1. Either or both your SELECT statements could be returning multiple rows. I think you need to sit down and 1: understand your data model and 2: understand your data.

To see what's going on more clearly, run these queries by themselves and look at the result sets:

SELECT (c.user_id + COALESCE(pi.user_id, 0) + COALESCE(p.user_id, 0))
  FROM courses c
  LEFT JOIN users u
      ON u.user_id = c.user_id
LEFT JOIN partners p
  ON p.partner_id = c.partner_id
LEFT JOIN partners_individual pi
  ON pi.individual_id = c.individual_id;
    WHERE c.course_id = CourseID;

SELECT user_type_id
  FROM users
  WHERE user_id = @user_id;

edited Sep 17, 2010 at 6:05

answered Sep 17, 2010 at 6:00

JohnB

19.1k17 gold badges102 silver badges114 bronze badges

Collectives™ on Stack Overflow

MySQL returning value from a SELECT statement

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related