CODEWITHSUNDEEP
mysqlsql-server
Anna Jeanine
Anna Jeanine

Reputation: 4125

Using alias in where clause SQL

Within the declaration of the SQL statement, I have implemented a if/else statement depending on the input of the user. This statement: 

    if($form['filter']['date'] && $form['filter']['dateTill'] == ""){
        $sql .= '(select results.completed from results where results.client_id = c.id and results.completed IS NOT NULL AND results.completed > "'.$form['filter']['date'].'" order by results.id desc limit 1) AS last_completed_analysis, ';
    } else if($form['filter']['dateTill'] && $form['filter']['dateTill'] == "") {
       $sql .= '(select results.completed from results where results.client_id = c.id and results.completed IS NOT NULL AND results.completed < "'.$form['filter']['dateTill'].'" order by results.id desc limit 1) AS last_completed_analysis, ';
    } else if($form['filter']['dateTill'] && $form['filter']['dateTill']){
        $sql .= '(select results.completed from results where results.client_id = c.id and results.completed IS NOT NULL AND results.completed > "'.$form['filter']['date'].'" AND results.completed <"'.$form['filter']['dateTill'].'" order by results.id desc limit 1) AS last_completed_analysis, ';
    }
    else {
        $sql .= '(select results.completed from results where results.client_id = c.id and results.completed IS NOT NULL order by results.id desc limit 1) AS last_completed_analysis, ';
    }

All of these are aliased as last_completed_analysis. However, the is not null is not working, thus I would want to declare a where clause with the is not null statement like: 

WHERE last_completed_analysis IS NOT NULL

However.. this isn't working. I could copy paste the whole declaration of the last_completed_analysis, but this code would be too long for me and I know it could be done better... could someone help me declare this where clause?

Upvotes: 2

Views: 168

Answers (1)

ScaisEdge
ScaisEdge

Reputation: 133400

You can't use alias in where condition

you can use alias in order by or in most recent version in group by but not in where ..

this because the columns area evaluated in a specific order and when the where condition is evaluated the alias are not ( at the moment) resolved

so you should use the full code eg:

  WHERE (select results.completed 
          from results 
          where results.client_id = c.id 
          and results.completed  
          IS NOT NULL order by results.id desc limit 1) IS NOT NULL

and be careful using string concat based un user input ..

Upvotes: 3

Related Questions