Cardinality violation on mysql query
mysql, sql
Solution
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
Problem
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