1242 - Subquery returns more than 1 row of MySQL

Question

I have a simple dummy sql with my dummy head :(

select if(`linktype`="group", (select contactgroups.grname from contactgroups, groupmembers on contactgroups.id=groupmembers.id),2) from groupmembers 

this list should only contain the name of the group, but I can’t see again what I did wrong :( Any help, please?

One more attempt:

 SELECT contactgroups.grname FROM contactgroups, groupmembers WHERE contactgroups.id = groupmembers.id 

works well, returns 2 group names. This is why the error message. But if I choose from groupmembers, it should match the groupmembers.group_id with the contact groups.id

Answer 1

if should return a scalar value. Never tested it, but try replacing contactgroups.grname with group_concat(contactgroups.grname)

Answer 2

Not sure why you include the groupMembers table in the subquery, but what about this alternative:

 SELECT IF(`linktype`="group", (SELECT contactgroups.grname FROM contactgroups WHERE contactgroups.id=groupmembers.id),2) FROM groupmembers 

or better yet, get rid of the subquery at all since it is not needed

 SELECT IF(`linktype`="group", contactgroups.grname,2) FROM groupmembers LEFT JOIN contactgroups ON (contactgroups.id = groupmembers.id) 

In addition, I have a suspicious suspicion that the table of your contact groups contains several records for one or more records in the table of your group. You can also confirm this.

Answer 3

Nested selection

 (select contactgroups.grname on contactgroups.id=groupmembers.id) 

seems to return more than 1 row, you can use LIMIT to limit this. Or, if this is not expected, check your details.