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

`(SELECT u.username FROM user u WHERE u.info_id IN (1,9,10,15,25,40,42,43,44))` returns more than one item, and cannot be handled. — zipser, Apr 01 '13 at 23:05

John Woo · Accepted Answer · 2013-04-01T23:21:22.030

12

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

edited Apr 01 '13 at 23:21

answered Apr 01 '13 at 23:12

John Woo

258,903
69
498
492

Why do you prefer the `JOIN` syntax? – B Seven Jul 21 '16 at 21:36
@BSeven [ANSI vs. non-ANSI SQL JOIN syntax](http://stackoverflow.com/questions/1599050/ansi-vs-non-ansi-sql-join-syntax) – John Woo Jul 23 '16 at 05:42

score 2 · Answer 2 · answered Apr 01 '13 at 23:12

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

score 1 · Answer 3 · answered Apr 01 '13 at 23:12

1

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

Learn to use joins, without them you are working wit both hands tied behind your back AND blindfolded. Joins are how to use the Relational part of Relational Databases.

answered Apr 01 '13 at 23:12

Dale M

2,453
1
13
21

The most compact yet readable, and the fact of not knowing Joins is *working with both hands tied behind your back* too. – edmundo096 Jan 26 '16 at 20:27

score 0 · Answer 4 · answered Apr 01 '13 at 23:15

0

Do you mean:

SELECT i.name, i.surname, (SELECT u.username FROM user u WHERE u.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)

answered Apr 01 '13 at 23:15

johnnaras

139
10

Cardinality violation on mysql query

4 Answers4