CODEWITHSUNDEEP
mysql
user2980769
user2980769

Reputation: 141

Query with WHERE / sub query containing multiple records

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.

Upvotes: 0

Views: 39

Answers (1)

sgeddes
sgeddes

Reputation: 62831

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

Upvotes: 1

Related Questions