CODEWITHSUNDEEP
phpmysqlsqlmysqli
j son
j son

Reputation: 47

How to handle an inner query returning multiple values in php mysql?

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....

Upvotes: 1

Views: 256

Answers (3)

peterm
peterm

Reputation: 92785

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 ...
)

Upvotes: 2

ozahorulia
ozahorulia

Reputation: 10084

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 ...
)

Upvotes: 2

MrVimes
MrVimes

Reputation: 3312

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.

Upvotes: 2

Related Questions