CODEWITHSUNDEEP
mysql
user1675246
user1675246

Reputation: 43

Assistance writing a mysql query based on conditions

I'm attempting to write a MySQL statement based on an if statement.

Here's what I want to do: I need the query to collect rows based on whether or not an item is going to be due within two weeks or if it's already passed due.

This is what I have so far:

select
  t_id,
  firstName, 
  lastName, 
  checkTime,
  due
from (checkOut join People)
where checkOut.p_id = People.p_id
order by due IF(DATE(NOW()-checkOut.due) <= 2 WEEK);

MySQL is stating that there is a syntax error near the IF statement (I'm not surprised as I'm not familiar with writing conditional statements in MySQL).

Another question is regarding whether or not MySQL will yield a date that is negative (i.e. to indicate that an item is passed due). Thanks in advance.

Upvotes: 1

Views: 65

Answers (2)

Shiplu Mokaddim
Shiplu Mokaddim

Reputation: 57650

I think what you are looking for is this,

SELECT t_id, 
       firstname, 
       lastname, 
       checktime, 
       due 
FROM   checkout 
       JOIN people 
         ON checkout.p_id = people.p_id 
ORDER  BY CASE 
            WHEN ( Now() - INTERVAL 2 week ) <= checkout.due THEN 0 
            ELSE 1 
          END, 
          due

Upvotes: 1

Michael Berkowski
Michael Berkowski

Reputation: 270607

The IF cannot be used as you have it. A portable alternative is to place a CASE in the ORDER BY to assign a 0 where the condition matches, and 1 which sorts after 0 when it doesn't.

select
  t_id,
  firstName, 
  lastName, 
  checkTime,
  due
from 
  checkOut 
  join People ON checkOut.p_id = People.p_id
order by 
  /* Use DATE_SUB() */
  /* This will sort those not yet past due ahead of those already past due. 
     If this is the opposite of what you want, switch the 0 and 1 */
  CASE WHEN DATE_SUB(CURDATE(), INTERVAL 2 WEEK) <= checkOut.due THEN 0 ELSE 1 END;

Note that I have converted your implicit join into an explicit one by changing the WHERE clause into the ON clause.

Upvotes: 1

Related Questions