CODEWITHSUNDEEP
phpmysqlsql
Amir
Amir

Reputation: 2149

SQL error: #1242 - Subquery returns more than 1 row

I got "error: #1242 - Subquery returns more than 1 row" trying to execute this Query:

SELECT id FROM postcodes WHERE pcd LIKE (SELECT CONCAT(pcd,' %') FROM towns WHERE id IN (31898,12828,15771,7604))

do you have any suggestion for this query?

Upvotes: 3

Views: 11649

Answers (3)

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79969

JOIN the two tables instead of the IN predicate, like this:

SELECT p.id 
FROM postcodes p
INNER JOIN towns t ON p.pcd LIKE CONCAT(t.pcd,' %')
WHERE t.id IN (31898,12828,15771,7604);

Upvotes: 3

Techie
Techie

Reputation: 45124

You have to LIMIT the sub-query to one row. Either You should add some filters to limit the result to one row or adding LIMIT 1 to your sub-query will do the trick for you.

Upvotes: 0

Iswanto San
Iswanto San

Reputation: 18569

Check this query :

SELECT CONCAT(pcd,' %') FROM towns WHERE id IN (31898,12828,15771,7604)

Maybe it's result is more than one row.

Or you can limit the subquery result.

SELECT id FROM postcodes WHERE pcd LIKE (SELECT CONCAT(pcd,' %') FROM towns WHERE id IN (31898,12828,15771,7604) LIMIT 1)

Upvotes: 1

Related Questions