Make MYSQL query shorter but with the same result

Olga
September 28, 2023
237 views
0 votes
2 Answers

I have a table in which the possible value is null, 0, 1 and 3. I have to select only the null and the value equal to 1 in strict order. The query I have is to long. Can you plese help me to make it shorter.

SELECT *
FROM cv_interview_status
WHERE 1
  AND (
        (`feedback_department` IS NULL)
        OR
        (
                `feedback_department` = 1
                AND `contacted_hr` IS NULL
                AND `office_interview` IS NULL
                AND `department_testing` IS NULL
                AND `department_acceptance` IS NULL
                AND `add_cv` IS NULL
                AND `psychological_test` IS NULL
                AND `department_interview` IS NULL
                AND `accepted_not_accepted` IS NULL
                AND `salary_request_to_ceo` IS NULL
                AND `ceo_feedback` IS NULL
                AND `salary_offer`IS NULL
                AND `accept_salary_offer` IS NULL
                AND `hr_documentation` IS NULL
                AND `hr_legal_requirements` IS NULL
            )
        OR
        (
                    `feedback_department` = 1
                AND `contacted_hr` =1
                AND `office_interview` IS NULL
                AND `department_testing` IS NULL
                AND `department_acceptance` IS NULL
                AND `add_cv` IS NULL
                AND `psychological_test` IS NULL
                AND `department_interview` IS NULL
                AND `accepted_not_accepted` IS NULL
                AND `salary_request_to_ceo` IS NULL
                AND `ceo_feedback` IS NULL
                AND `salary_offer`IS NULL
                AND `accept_salary_offer` IS NULL
                AND `hr_documentation` IS NULL
                AND `hr_legal_requirements` IS NULL
            )
        OR
        (
                    `feedback_department` = 1
                AND `contacted_hr` = 1
                AND `office_interview` = 1
                AND `department_testing` IS NULL
                AND `department_acceptance` IS NULL
                AND `add_cv` IS NULL
                AND `psychological_test` IS NULL
                AND `department_interview` IS NULL
                AND `accepted_not_accepted` IS NULL
                AND `salary_request_to_ceo` IS NULL
                AND `ceo_feedback` IS NULL
                AND `salary_offer`IS NULL
                AND `accept_salary_offer` IS NULL
                AND `hr_documentation` IS NULL
                AND `hr_legal_requirements` IS NULL
            )
        OR
        (
                    `feedback_department` = 1
                AND `contacted_hr` =1
                AND `office_interview` = 1
                AND `department_testing` = 1
                AND `department_acceptance` IS NULL
                AND `add_cv` IS NULL
                AND `psychological_test` IS NULL
                AND `department_interview` IS NULL
                AND `accepted_not_accepted` IS NULL
                AND `salary_request_to_ceo` IS NULL
                AND `ceo_feedback` IS NULL
                AND `salary_offer`IS NULL
                AND `accept_salary_offer` IS NULL
                AND `hr_documentation` IS NULL
                AND `hr_legal_requirements` IS NULL
            )
        OR
        (
                    `feedback_department` = 1
                AND `contacted_hr` =1
                AND `office_interview` = 1
                AND `department_testing` = 1
                AND `department_acceptance` = 1
                AND `add_cv` IS NULL
                AND `psychological_test` IS NULL
                AND `department_interview` IS NULL
                AND `accepted_not_accepted` IS NULL
                AND `salary_request_to_ceo` IS NULL
                AND `ceo_feedback` IS NULL
                AND `salary_offer`IS NULL
                AND `accept_salary_offer` IS NULL
                AND `hr_documentation` IS NULL
                AND `hr_legal_requirements` IS NULL
            )
        OR
        (
                    `feedback_department` = 1
                AND `contacted_hr` =1
                AND `office_interview` = 1
                AND `department_testing` = 1
                AND `department_acceptance` = 1
                AND `add_cv` = 1
                AND `psychological_test` IS NULL
                AND `department_interview` IS NULL
                AND `accepted_not_accepted` IS NULL
                AND `salary_request_to_ceo` IS NULL
                AND `ceo_feedback` IS NULL
                AND `salary_offer`IS NULL
                AND `accept_salary_offer` IS NULL
                AND `hr_documentation` IS NULL
                AND `hr_legal_requirements` IS NULL
            )
        OR
        (
                    `feedback_department` = 1
                AND `contacted_hr` =1
                AND `office_interview` = 1
                AND `department_testing` = 1
                AND `department_acceptance` = 1
                AND `add_cv` = 1
                AND `psychological_test` = 1
                AND `department_interview` IS NULL
                AND `accepted_not_accepted` IS NULL
                AND `salary_request_to_ceo` IS NULL
                AND `ceo_feedback` IS NULL
                AND `salary_offer`IS NULL
                AND `accept_salary_offer` IS NULL
                AND `hr_documentation` IS NULL
                AND `hr_legal_requirements` IS NULL
            )
        OR
        (
                    `feedback_department` = 1
                AND `contacted_hr` =1
                AND `office_interview` = 1
                AND `department_testing` = 1
                AND `department_acceptance` = 1
                AND `add_cv` = 1
                AND `psychological_test` = 1
                AND `department_interview` = 1
                AND `accepted_not_accepted` IS NULL
                AND `salary_request_to_ceo` IS NULL
                AND `ceo_feedback` IS NULL
                AND `salary_offer`IS NULL
                AND `accept_salary_offer` IS NULL
                AND `hr_documentation` IS NULL
                AND `hr_legal_requirements` IS NULL
            )
        OR
        (
                    `feedback_department` = 1
                AND `contacted_hr` =1
                AND `office_interview` = 1
                AND `department_testing` = 1
                AND `department_acceptance` = 1
                AND `add_cv` = 1
                AND `psychological_test` = 1
                AND `department_interview` = 1
                AND `accepted_not_accepted` = 1
                AND `salary_request_to_ceo` IS NULL
                AND `ceo_feedback` IS NULL
                AND `salary_offer`IS NULL
                AND `accept_salary_offer` IS NULL
                AND `hr_documentation` IS NULL
                AND `hr_legal_requirements` IS NULL
            )
        OR
        (
                    `feedback_department` = 1
                AND `contacted_hr` =1
                AND `office_interview` = 1
                AND `department_testing` = 1
                AND `department_acceptance` = 1
                AND `add_cv` = 1
                AND `psychological_test` = 1
                AND `department_interview` = 1
                AND `accepted_not_accepted` = 1
                AND `salary_request_to_ceo` = 1
                AND `ceo_feedback` IS NULL
                AND `salary_offer`IS NULL
                AND `accept_salary_offer` IS NULL
                AND `hr_documentation` IS NULL
                AND `hr_legal_requirements` IS NULL
            )
        OR
        (
                    `feedback_department` = 1
                AND `contacted_hr` =1
                AND `office_interview` = 1
                AND `department_testing` = 1
                AND `department_acceptance` = 1
                AND `add_cv` = 1
                AND `psychological_test` = 1
                AND `department_interview` = 1
                AND `accepted_not_accepted` = 1
                AND `salary_request_to_ceo` = 1
                AND `ceo_feedback` = 1
                AND `salary_offer`IS NULL
                AND `accept_salary_offer` IS NULL
                AND `hr_documentation` IS NULL
                AND `hr_legal_requirements` IS NULL
            )
        OR
        (
                    `feedback_department` = 1
                AND `contacted_hr` =1
                AND `office_interview` = 1
                AND `department_testing` = 1
                AND `department_acceptance` = 1
                AND `add_cv` = 1
                AND `psychological_test` = 1
                AND `department_interview` = 1
                AND `accepted_not_accepted` = 1
                AND `salary_request_to_ceo` = 1
                AND `ceo_feedback` = 1
                AND `salary_offer` = 1
                AND `accept_salary_offer` IS NULL
                AND `hr_documentation` IS NULL
                AND `hr_legal_requirements` IS NULL
            )
        OR
        (
                    `feedback_department` = 1
                AND `contacted_hr` =1
                AND `office_interview` = 1
                AND `department_testing` = 1
                AND `department_acceptance` = 1
                AND `add_cv` = 1
                AND `psychological_test` = 1
                AND `department_interview` = 1
                AND `accepted_not_accepted` = 1
                AND `salary_request_to_ceo` = 1
                AND `ceo_feedback` = 1
                AND `salary_offer` = 1
                AND `accept_salary_offer` = 1
                AND `hr_documentation` IS NULL
                AND `hr_legal_requirements` IS NULL
            )
    );

The filter for this table works perfect, but the query is to long, can you give me an advise how can I refactor it?

Tags: mysql sql

Answers

SELECT
    * 
FROM
    cv_interview_status 
WHERE
    (`feedback_department` IS NULL) 
    OR (
        `feedback_department` = 1 
        AND (
            `contacted_hr` IS NULL 
            OR `contacted_hr` = 1 
            OR `office_interview` IS NULL 
            OR `office_interview` = 1 
            OR `department_testing` IS NULL 
            OR `department_testing` = 1 
            OR `department_acceptance` IS NULL 
            OR `department_acceptance` = 1 
            OR `add_cv` IS NULL 
            OR `add_cv` = 1 
            OR `psychological_test` IS NULL 
            OR `psychological_test` = 1 
            OR `department_interview` IS NULL 
            OR `department_interview` = 1 
            OR `accepted_not_accepted` IS NULL 
            OR `accepted_not_accepted` = 1 
            OR `salary_request_to_ceo` IS NULL 
            OR `salary_request_to_ceo` = 1 
            OR `ceo_feedback` IS NULL 
            OR `ceo_feedback` = 1 
            OR `salary_offer` IS NULL 
            OR `salary_offer` = 1 
            OR `accept_salary_offer` IS NULL 
            OR `accept_salary_offer` = 1 
        ) 
        AND `hr_documentation` IS NULL 
      AND `hr_legal_requirements` IS NULL 
    );

- Akina
- September 28, 2023 at 8:17 am
- 0 votes
0
```
...
WHERE ...
  AND CONCAT( COALESCE(feedback_department, 0),
              COALESCE(contacted_hr, 0),
              ...
              COALESCE(hr_legal_requirements, 0)
              ) REGEXP '^1{1,}0{2,}$'
...
```
Login or Signup to reply.

Please signup or login to give your own answer.

Click here to cancel reply.