CODEWITHSUNDEEP
mysqlsql
John Siniger
John Siniger

Reputation: 885

Mysql HAVING clause with condition if it is possible

Hello I would like to know if the following HAVING clause is valid 

SELECT
  m.id,
  m.username,
  m.name,
  m.department,
  dp.department as dep,
  dp.id,
  (
    SELECT SEC_TO_TIME( SUM( TIME_TO_SEC( `time` ) ) )
    FROM time_management tm
    WHERE 
      tm.user = m.id
      AND MONTH(`date`) = MONTH(CURRENT_DATE())
      AND YEAR(`date`)= YEAR(CURRENT_DATE())
  ) as time
FROM
  members m 
  LEFT JOIN department dp ON m.department = dp.id   
WHERE m.department = $departmentvar
HAVING (($key - time) > 34)

in this case $key = 100 

time = 5:00:00

The idea is to select only results which satisfies the condition that their time is not enough , for example

display results which have time = 5:00:00 because (100-5) = 95 > 34 but does not display results which have time = 95:00:00 because (100-95) = 5 > 34 for example ?

Upvotes: 0

Views: 1608

Answers (1)

spencer7593
spencer7593

Reputation: 108410

Yes, it is possible to include expressions in a HAVING clause.

But MySQL isn't going to know what to do with the token $key, if that's part of the SQL text being sent.

Perhaps the client program is doing some variable substitution to generate a string, and that $key is getting replaced with 100 in the string that is going to be sent to MySQL... that's an assumption we're likely to make here. But it's an assumption. It appears that you are asking about the SQL statement that is being sent to MySQL.

As far as returning only rows where "time is not enough", it's certainly possible to have that condition in the HAVING clause.

The time column in the resultset is going to be datatype TIME.

But I'm confused by this construct... 

  100 - time  > 34

Why would be subtracting TIME datatype from a numeric literal?

If we want to compare the TIME datatype, it would make more sense to compare that to another TIME datatype, or convert the TIME into a number of seconds.

(It's not at all clear what the 100 and 34 represent.)

Seems like you want to do something like:

 HAVING time < '01:06:00'

or

 HAVING time < SEC_TO_TIME(( 100 - 34 )*60)

Or

 HAVING ( 100 - TIME_TO_SEC(time) ) > 34

It's not clear what you are attempting to achieve, so those are just some patterns that appear to be more valid than the original.

Upvotes: 1

Related Questions