Cardinality violation on mysql query

Question

Im getting an error on this query:

SELECT i.name, i.surname, (SELECT u.username FROM user u WHERE u.info_id IN (1,9,10,15,25,40,42,43,44)) as email FROM `userinfo` i WHERE i.id IN (1,9,10,15,25,40,42,43,44)

Error:

Cardinality violation: 1242 Subquery returns more than 1 row

I know this could be solved by using a JOIN statment but i have no idea how to solve that.

To clarify more my question, I have two tables, user and userinfo:

User

id info_id username

Userinfo

id name surname 

The info_id row from user is related to the id of userinfo, so having a list of userinfo ids in this format (1,4,7,8,9) I want name and surname (from userinfo) and username from user that match the info_id

Answer 1

You need to specify the relationship between the two table in the correlated subquery,

SELECT  i.name, 
        i.surname, 
        (
            SELECT  u.username 
            FROM    user u 
            WHERE   u.info_id = i.id) as email 
FROM    userinfo i 
WHERE   i.id IN (1,9,10,15,25,40,42,43,44)

and by using JOIN (which I preferred more)

SELECT  i.name, 
        i.surname, 
        u.username as Email
FROM    userinfo i 
        INNER JOIN user u 
            ON u.info_id = i.id
WHERE   i.id IN (1,9,10,15,25,40,42,43,44)

To further gain more knowledge about joins, kindly visit the link below:

• Visual Representation of SQL Joins

Answer 2

You're only allowed to return one row in a field list. It seems like you want to group the results.

SELECT
    i.name, i.surname,
    GROUP_CONCAT(u.username) AS email
FROM
    userinfo i
    JOIN user u ON (i.id = u.info_id)
WHERE
    i.id IN (1,9,10,15,25,40,42,43,44)
GROUP BY
    i.id