CODEWITHSUNDEEP
mysqlsql
Sam
Sam

Reputation: 51

Case subquery returning too many rows within where clause

Hi what I am trying to achieve is a query which has a dynamic column name in the where clause depending on whether a column is null or not.

So as an example, if a row has an Appointment Date which is not null, the where clause will be:

WHERE `Building ID` = '1' and `Appointment Date`='2013-10-10' ;

And if the Appointment Date is null, the where clause will be:

WHERE `Building ID` = '1' and `Backup Date`='2013-10-10' ;

Right now my subquery in the where clause is returning too many rows so the query fails, how should I get around this issue?

My query is below:

SELECT `Job ID` 
FROM jobs 
WHERE `Building ID` = '1' 
and (select case when `Appointment Date` IS NOT NULL THEN `Appointment Date` 
else `Backup Date` end FROM jobs WHERE `Building ID` = '1') = '2013-10-10'

Upvotes: 0

Views: 392

Answers (3)

Alden
Alden

Reputation: 6703

Try this:

SELECT `Job ID` 
FROM jobs 
WHERE `Building ID` = '1' 
AND ((`Appointment Date` IS NOT NULL AND `Appointment Date` = '2013-10-10') 
      OR
     (`Appointment Date` IS NULL AND `Backup Date` = '2013-10-10'))

Upvotes: 0

fancyPants
fancyPants

Reputation: 51878

Use the COALESCE() function. It returns the first of its parameters which is not null.

WHERE `Customer ID` = '1' and COALESCE(`Appointment Date`, `Backup Date`) ='2013-10-10' ;

Upvotes: 1

juergen d
juergen d

Reputation: 204756

SELECT `Job ID` 
FROM jobs 
WHERE `Building ID` = '1' 
and case when `Appointment Date` IS NOT NULL 
         then `Appointment Date` = '2013-10-10' 
         else `Backup Date` = '2013-10-10'
    end

Upvotes: 1

Related Questions