CODEWITHSUNDEEP
mysqlselectif-statement
XTRUST.ORG
XTRUST.ORG

Reputation: 3402

Query with IF statement

I have an question about MYSQL If statement. My request: 

SELECT c.status, c.thumb, j.id, j.name, j.username, s.session_id, a.time, a.isactive, a.country, a.countrycode, a.city
FROM j16_users AS j 
JOIN j16_community_users AS c ON c.userid = j.id 
LEFT JOIN j16_session AS s ON s.userid = j.id 
LEFT JOIN j16_session AS s ON s.userid = j.id 
LEFT JOIN j16_x5_fastchat_users AS a ON a.userid = j.id 
WHERE j.id IN (62,66,2692)

How can I add a new column: isonline with condition to above request:

IF(a.time > DATE_SUB(NOW(), INTERVAL 1 MINUTE), "1", "0") AS isonline

Thanks!

Upvotes: 0

Views: 90

Answers (2)

Michael Berkowski
Michael Berkowski

Reputation: 270775

Just add it into the SELECT list. A CASE statement is recommended as more portable though.

SELECT 
  c.status, 
  c.thumb, 
  j.id, 
  j.name, 
  j.username, 
  s.session_id, 
  a.time, 
  a.isactive, 
  a.country, 
  a.countrycode, 
  a.city,
  CASE WHEN a.time > DATE_SUB(NOW(), INTERVAL 1 MINUTE) THEN 1 ELSE 0 END AS isonline
FROM
  j16_users AS j 
  JOIN j16_community_users AS c ON c.userid = j.id 
  LEFT JOIN j16_session AS s ON s.userid = j.id 
  LEFT JOIN j16_session AS s ON s.userid = j.id 
  LEFT JOIN j16_x5_fastchat_users AS a ON a.userid = j.id 
WHERE 
  j.id IN (62,66,2692)

Since you just need a boolean 1 or 0, the statement can also be written without CASE or IF as:

 ... 
 a.countrycode, 
 a.city,
 (a.time > DATE_SUB(NOW(), INTERVAL 1 MINUTE)) AS isonline
 ...

and it will have the same effect.

Upvotes: 1

John Woo
John Woo

Reputation: 263933

SELECT c.status, c.thumb, 
       j.id, j.name, 
       j.username, s.session_id, 
       a.time, a.isactive, 
       a.country, a.countrycode, a.city,
       IF(a.time > DATE_SUB(NOW(), INTERVAL 1 MINUTE), 1, 0) AS isOnline
FROM j16_users AS j 
     JOIN j16_community_users AS c ON c.userid = j.id 
     LEFT JOIN j16_session AS s ON s.userid = j.id 
     LEFT JOIN j16_session AS s ON s.userid = j.id 
     LEFT JOIN j16_x5_fastchat_users AS a ON a.userid = j.id 
WHERE j.id IN (62,66,2692)

Upvotes: 1

Related Questions