CODEWITHSUNDEEP
mysqlfunction
Helen Postle
Helen Postle

Reputation: 45

mysql function always returns null even though no null answers

my function is as follows:

DELIMITER $$

CREATE DEFINER=`root`@`localhost` FUNCTION `get_year_groups_for_visit`(id int) RETURNS varchar(200) CHARSET latin1
BEGIN
declare year_text varchar(200);

    select group_concat(year_text, ' ') into year_text from a_visit_year_group yg
join year_group y on yg.year_group_id = y.year_group_id
where yg.visitid = id;

RETURN year_text;

END

if I call this using 

select get_year_groups_for_visit(1918)

I get the answer null but if I type in 

select group_concat(year_text, ' ') as 'Year Groups' from a_visit_year_group yg
join year_group y on yg.year_group_id = y.year_group_id
where yg.visitid = 1918;

I get the answer 'Year 13, Year 12'

if I look in the table a_visit_year_group where visitid is 1918 I get 

> visitid   year_group_id
   1918      14
   1918      15

and in the table year_group I get

> year_group_id     year_text 
  14                Year 12 
  15                Year 13

In fact it is not possible to have an entry in a_visit_year_group with a null value for either year_group_id or visitid and it is not possible to have an entry in the table year_group_id with a null value for year_group_id or year_text so I can't understand why I only get null values back using this function. I have tested it where there is only one value to come back and where there are several values and it always comes back as null. I know if you concat values and one value is null, the answer is always null, but there really are no null answers here.

I'm starting to pull my hair out now as I can't see where I am going wrong, bound to be something really simple I am missing. I would really appreciate some insight. I need to get a list of visits and the year groups involved in each visit and sometimes there are several year groups for each visit and sometimes there are only 1, I don't want multiple rows for each visit so I thought I could create a function to return all the values as one

Upvotes: 0

Views: 237

Answers (1)

Solarflare
Solarflare

Reputation: 11106

Your variable has the same name as your column year_text; in your query, it will not refer to the column, but to the variable both times, so you are concatenating the variable here.

To quote the documentation:

A local variable should not have the same name as a table column. If an SQL statement, such as a SELECT ... INTO statement, contains a reference to a column and a declared local variable with the same name, MySQL currently interprets the reference as the name of a variable.

Either (preferable) use a different name for your variable, or add the table(-alias) to the column to get the right scope: 

select group_concat(y.year_text, ' ') into year_text 
from ... join year_group y ...

Upvotes: 2

Related Questions