How to handle an inner query returning multiple values in php mysql?
For eg.
SELECT value1 FROM table1 WHERE value1 != (SELECT value1 FROM table2 WHERE .....)
The problem is (SELECT value1 FROM table2 WHERE .....) is returning multiple values.
How to solve this?
Please help me....
Assuming that you want to use a subquery to limit the resultset of an outer query you can do that using IN or EXISTS
SELECT value1
FROM table1
WHERE value1 NOT IN
(
SELECT value1
FROM table2
WHERE value1 IS NOT NULL ...
)
Be careful with NOT IN if subquery returns NULLs you won't get an expected result (an empty resultset). Either make sure that a subquery doesn't return NULLs (with appropriate WHERE clause or substitute NULLS with some value using COALESCE or IFNULL) or use NOT EXISTS
SELECT value1
FROM table1 t
WHERE NOT EXISTS
(
SELECT 1
FROM table2
WHERE value1 = t.value1 ...
)
Here is SQLFiddle demo. Note that the first query in the demo doesn't return any rows.
On the other hand if you just need your subquery to return the only value
1) Use LIMIT 1 as already suggested
2) or an appropriate aggregate function (MIN(), MAX(), AVG() etc)
SELECT value1
FROM table1
WHERE value1 <>
(
SELECT MAX(value1)
FROM table2
WHERE ...
)
Put "limit 1" at the end of your inner query. This will tell the query engine to only return the first result.
But if you want to compare your value against multiple results in the subquery, use peterm's solution.
SELECT value1
FROM table1
WHERE value1 != (
SELECT value1 FROM table2 WHERE ... LIMIT 1
)
But make sure that subquery returns exactly what you need. Because since it returns multile values instead of one, you probably have some logic misunderstoods.
Also, if you need value1 to not equal several parameters instead of one, you can use NOT IN
SELECT value1
FROM table1
WHERE value1 NOT IN (
SELECT value1 FROM table2 WHERE ...
)
