CODEWITHSUNDEEP
mysqlsql
boski
boski

Reputation: 1146

Subquery returns more than 1 row in MySQL procedure

I am working on db in MySQL and need data from few tables in one row. It's problem connected with relative databases:

CREATE TABLE pet (id INT, name VARCHAR(20));
insert into pet values (1,"Rufus");
insert into pet values (2,"Bali");
insert into pet values (3,"Lolo");

CREATE TABLE own (id INT, own_name VARCHAR(20), own_color VARCHAR(20));
insert into own values (1,"Me", "Red");
insert into own values (2,"Other owners" ,"Green");

CREATE TABLE pet_owner (id INT, id_pet INT, id_own INT);
insert into pet_owner values (1, 1, 1);
insert into pet_owner values (2, 2, 1);
insert into pet_owner values (3, 3, 2);

DROP procedure if exists `pet`;
DELIMITER $$
CREATE procedure `pet`() 
BEGIN
set @param = 1;
select
a.own_color as 'color',
(select id_pet from pet_owner where id_own = @param) as 'pets'

from own as a where a.id = @param;
END$$

call pet;

Subquery returns more than 1 row

How to collect all pets id's in one row (every id_pet can be in other column)

Upvotes: 0

Views: 2453

Answers (2)

Abdou Tahiri
Abdou Tahiri

Reputation: 4408

You can use GROUP_CONCAT.

-- ...
select
a.own_color as 'color',
(select group_concat(id_pet,',') from pet_owner where id_own = @param) as 'pets'
from own as a where a.id = @param;

but beware that there is a 1024 byte limit on result. to solve this run this query before your query: (change the 2048 according to your needs) .

set group_concat_max_len=2048

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269943

You want to use group_concat():

select o.own_color as color,
       (select group_concat(id_pet) from pet_owner po where po.id_own = @param) as pets
from own o
where o.id = @param;

Note that I made a couple of changes to the query:

  • The table aliases are abbreviations for the table name, instead of useless letters (o for owners, instead of a).
  • The column aliases are not surrounded by single quotes. Only use single quotes for string and date constants. If you must escape identifiers, use double quotes or backticks.
  • Added the group_concat()

Upvotes: 4

Related Questions