LearntoExcel LearntoExcel - 3 years ago 141
SQL Question

Issues with SQL ERROR: subquery returned more than 1 row

I am trying to devise an SQL statement where I am getting said error in the subject. I understand that there are other topics on this error but have not seen any regarding my specific problem where I am trying to get a value based off multiple rows. I would appreciate any insight. Here are my simplified Tables and SQL statement.

TableA:

member_id
----------
1
2
3
4


TableB:

user_id field_id value
------------------------
1 1 red
2 2 red
1 2 blue
4 1 red
2 1 blue
3 1 blue


Query:

SELECT DISTINCT(member_id)
FROM TableA
WHERE member_id = (SELECT b.user_id
FROM TableB b
INNER JOIN TableB a on a.user_id = b.user_id
WHERE a.field_id = '1' ANd a.value = 'red'
AND b.field_id = '2' ANd b.value = 'blue')


I am trying to select the
member_id
from table A as the
user_id
in Table B where in table B the user's field_id(1) = red and the same user's field_id(2) = blue.

Therefore the select statement should return the member_id with id = 1.

Answer Source

Use the query you had for the sub select and just JOIN tableA.

SELECT a.*
FROM TableB b1 
INNER JOIN TableB b2 on b1.user_id = b2.user_id 
INNER JOIN tableA a ON a.member_id  = b1.user_id
WHERE b2.field_id = '1' ANd b2.value = 'red' 
  AND b1.field_id = '2' ANd b1.value = 'blue'
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download