# 1242 - Subquery returns more than 1 row - mysql

Question

I am trying to make a select statement that selects image names from a MySQL database. The table is called - pictures_archive. I am also trying to select these photos depending on the category they have. Code:

SELECT pictures_archive_filename FROM pictures_archive WHERE pictures_archive_id = (SELECT pictures_archive_id FROM pictures_archive_category WHERE pictures_category_id = 9) 

This gives me error # 1242 - Subquery returns more than 1 row. "I understand why, but can't figure out how to do this.

Answer 1

Since your subquery can return multiple values, IN should fit into the youy where clause.

 SELECT pictures_archive_filename FROM pictures_archive WHERE pictures_archive_id IN ( SELECT pictures_archive_id FROM pictures_archive_category WHERE pictures_category_id = 9 ) 

an alternative for this is to join both tables, which are more efficient.

 SELECT pictures_archive_filename FROM pictures_archive a INNER JOIN pictures_archive_category b ON a.pictures_archive_id = b.pictures_archive_id WHERE b.pictures_category_id = 9 

Answer 2

Use IN instead of equality (=)

 SELECT pictures_archive_filename FROM pictures_archive WHERE pictures_archive_id IN (SELECT pictures_archive_id FROM pictures_archive_category WHERE pictures_category_id = 9) 

OR, if possible, use a JOIN between two tables

Answer 3

 SELECT pictures_archive_filename FROM pictures_archive WHERE pictures_archive_id = (SELECT pictures_archive_id FROM pictures_archive_category WHERE pictures_category_id = 9 LIMIT 1) 

Answer 4

 SELECT p.pictures_archive_filename FROM pictures_archive p inner join pictures_archive_category pc on p.pictures_archive_id = pc.pictures_archive_id where pc.pictures_category_id=9