CODEWITHSUNDEEP
sqlhibernatenull
r15habh
r15habh

Reputation: 1468

Handling null in subquery

SELECT *
  FROM myTable m
 WHERE m.userId = :userId
   AND m.X = (SELECT MAX(X) 
                FROM myTable m
               WHERE m.userId = :userId 
                 AND m.contactNumber = :contactNumber)";

The problem is,second part of statement evaluates to null in case no such row is present and the statement fails to execute.I want the result to be empty in such a case.

One way to solve this problem is to do expensive filesort(order by) and then fetch the required field at code level. Any better solution to this problem ?

Upvotes: 1

Views: 710

Answers (2)

Bacon Bits
Bacon Bits

Reputation: 32180

I'm not sure why you're getting NULLs here, but try this:

SELECT myTable.*, IF myTableMax.myMaxX IS NOT NULL myTableMax.myMaxX ELSE ""
  FROM myTable
LEFT OUTER JOIN 
       (SELECT userID, contactNumber, MAX(X) AS myMaxX
          FROM myTable
      GROUP BY userID, contactNumber) AS myTableMax
    ON myTable.userID = myTableMax.userID
   AND myTable.contactNumber = myTableMax.contactNumber
 WHERE myTable.userID = :userID
   AND myTable.contactNumber = :contactNumber

If you're concerned about performance, add an index on mytable (userID, contactNumber).

Upvotes: 0

Paul McLean
Paul McLean

Reputation: 3560

Can you use ISNULL?

and m.X = ISNULL(, '')

Upvotes: 2

Related Questions