Query with WHERE / sub query containing multiple records

Question

This is my query:

SELECT * FROM img_ref WHERE id_img = (SELECT `id_img` FROM img_ref WHERE `id_user`=4)

I'm trying to SELECT records from img_ref WHERE id_img = id_img attached to a specific id_user.

However, the subquery contains multiple rows/results and therefore throws the error "Subquery returns more than one row". How could I modify this statement so that the "WHERE id_img=" part and subquery allow multiple rows?

Thanks for your help in advance.

score 1 · Accepted Answer · edited May 23 '17 at 12:03

1

When using a subquery with equals, the subquery can only return a single record. Normally you would just use IN to return multiple records:

SELECT * 
FROM img_ref 
WHERE id_img IN (SELECT `id_img` FROM img_ref WHERE `id_user`=4)

For performance reasons (check this post), you may want to move this to a JOIN:

SELECT i.*
FROM img_ref i
    JOIN img_ref i2 ON i.id_img = i2.id_img 
        AND i2.id_user=4

SQL Fiddle Demo

edited May 23 '17 at 12:03

Community

1
1

answered Jan 31 '14 at 18:40

sgeddes

62,311
6
61
83

The sub query is needed because id_img can be related to multiple id_user so I'm trying to check which other id_user's are in that table with the same id_img. – user2980769 Jan 31 '14 at 18:43
Is there any way to optimize this query better, IN seems to be very slow (1.5secs)? Thanks for your help. – user2980769 Jan 31 '14 at 18:44
@user2980769 -- ok, that makes sense. I'll remove that. – sgeddes Jan 31 '14 at 18:44
@user2980769 -- I edited the solution to include an alternative answer. Best regards! – sgeddes Jan 31 '14 at 18:51

Query with WHERE / sub query containing multiple records

1 Answers1