MySQL - Specifics conditions for my request

Question

I work since this week on this request but I can't find any solution to solve my problem.

SELECT 
  client, summary, description, reporter, assignee,
  CASE lsttf.type_f
    WHEN 'O' THEN prmij.date_and_time
    WHEN 'H' THEN DATE_ADD(now(), INTERVAL 7 DAY)
  END as dateExecution

FROM PRM_import_Jira prmij
INNER JOIN LST_frequence lstf on prmij.fk_frequences = lstf.id
INNER JOIN LST_type_frequence lsttf ON lstf.fk_type_frequence = lsttf.id
HAVING lsttf.type_f = 'O' and dateExecution = DATE_ADD(now(),INTERVAL 7 DAY)
    OR lsttf.type_f = 'H' and lstf.dayValue like concat('%',(weekday(now()+1)),'%');

I check some informations on the Web but I can"t find what I want because I have "specifics conditions" for this :

When I execute my request, I want only this 5 columns however, "I can't" specify lsttf.type_f and lstf.dayValue on my SELECT condition to use HAVING.
I would like to use my Alias dateExecution but I can't with the WHERE rules (explain here).

Thanks for your help.

Restructure the query to use what you have in the "having" as the primary table vs being joined. Should really do this anyway as mySQL's execution schema doesn't like figuring out joined indexes when they're used in the main WHERE\HAVING clause — Mason Stedman, Oct 18 '18 at 09:41

score 0 · Accepted Answer · answered Oct 18 '18 at 09:40

Try this:

SELECT client, summary, description, reporter, assignee
FROM (
    SELECT client, summary, description, reporter, assignee, lsttf.type_f, lstf.dayValue
        case lsttf.type_f
        when 'O' then prmij.date_and_time
        when 'H' then DATE_ADD(now(), INTERVAL 7 DAY)
        end as dateExecution

    FROM PRM_import_Jira prmij
    INNER JOIN LST_frequence lstf on prmij.fk_frequences = lstf.id
    INNER JOIN LST_type_frequence lsttf ON lstf.fk_type_frequence = lsttf.id
    HAVING lsttf.type_f = 'O' 
    AND (case lsttf.type_f
        when 'O' then prmij.date_and_time
        when 'H' then DATE_ADD(now(), INTERVAL 7 DAY)
        end) = DATE_ADD(now(), INTERVAL 7 DAY)
    OR lsttf.type_f = 'H' and lstf.dayValue like concat('%',(weekday(now()+1)),'%')
) x

The having clause can only use the columns determined in the select statement and SQL doesn't let you use alias' in the where clause

MySQL - Specifics conditions for my request

1 Answers1