[SOLVED] BASIC SELECT QUERY USING IF, ELSE

[webmaster1]

Hi All,

 

I'm using a basic select query in my php page on two tables at the same time. You'll notice I've used an if, then, else condition succesfully:

 

IF( tablename1.column1 IS NULL , 'Value', tablename1.column1 ) AS columnname

 

SELECT * 
FROM (
      SELECT masterdata.masterdataid,
             masterdata.companyname, 
             masterdata.contactname, 
             IF( resultsnotations.prospectrating IS NULL , 'Not Rated', resultsnotations.prospectrating ) AS prospectrating, 
            resultsnotations.lastactivity
      FROM masterdata
      LEFT JOIN resultsnotations ON masterdata.masterdataid = resultsnotations.masterdataid
      WHERE category = 'CIF'
      ORDER BY masterdata.masterdataid, resultsnotations.lastactivity DESC 
     ) 
AS a GROUP BY masterdataid

 

The problem occurs when I try to use a similar condition for the lastactivity column. I get the following error:

 

MySQL said: 

 

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IF( resultsnotations.lastactivity IS NULL , 'Loaded', resultsnotations.lastactiv' at line 9

 

The only thing different this time around is the fact that I'm calling a value from the second table rather than the first. Can somebody please help me spot where I'm going wrong?

 

 

[Mchl]

Using IFNULL may not help here, but will certainly look better.

 

And check for any missing commas before this IF

[webmaster1]

Thanks mchl. That's a lot easier on the eye. I discvovered the issue while making those changes too (typos).

 

Functioning code:

SELECT * 
FROM (
      SELECT masterdata.masterdataid,
             masterdata.companyname, 
             masterdata.contactname, 
             IFNULL( resultsnotations.prospectrating, 'Not Rated') AS prospectrating,
             IFNULL( resultsnotations.lastactivity, 'Loaded') AS lastactivity
      FROM masterdata
      LEFT JOIN resultsnotations ON masterdata.masterdataid = resultsnotations.masterdataid
      WHERE category = 'CIF'
      ORDER BY masterdata.masterdataid, resultsnotations.lastactivity DESC 
     ) 
AS a GROUP BY masterdataid