Question 21762 in Mysql Question posted in
The official MySQL documentation can be found here.

Dependent 2 queries on "same" column in Mysql

I have a table like below

report_code | result_of_report
_______________________________
Rpx-34512-00| rejected
Rpx-34512-01| rejected
Rpx-34512-02| rejected
Rpt-22433-00| rejected
Rpt-22433-01| rejected
Rpt-22433-02| rejected
Rpt-22433-03| rejected
Rpt-22433-04| rejected
Rpt-22433-05| rejected
Rpt-22433-06| accepted
Rcs-34555-00| rejected
Rcs-34555-01| rejected
Rcs-34555-02| accepted

And this is what I want to get: For report_code I need to substr(report_code, 4,5) to 5 digit report code and look for reports that haven’t yet accepted.

6th revision (it doesn’t matter which version accepted btw.) of 22433 has a record of accepted. So it passed. 34555 get accepted in second revision. It passed too. BUT 34512 doesn’t have any accepted record. I want to list all records like 34512 but with groups. So I don’t want to list many duplicated line. To make it more clear, no need to list 34512 3 times. One will be enough.

First of all I want to explain what I tried. I know it doesn’t the right way but it was a temporary solution for me.

I ran a loop to take where result_of_report='rejected' and inside this loop, I took that 5 digit report_code as $code and I used that code in a loop as where(report_code, 'like', '$code) AND where(result_of_report, '=', 'accepted') inside of the first one to make query for every record. 🤦‍♂️

As you can see, it’s not the right way but also I don’t know the right way too. I’m open to any idea to make it in a most efficient way. Don’t want to make millions of loop iteration. Thanks for any help in advance.

Tags:

2

Answers


  1. 0

    You could group by SUBSTR(report_code, 5,5) and use conditional aggregation as the following:

    SELECT SUBSTR(report_code, 5,5) AS report_code
FROM table_name
GROUP BY SUBSTR(report_code, 5,5)
HAVING COUNT(CASE WHEN result_of_report='accepted' THEN 1 END) = 0

    HAVING COUNT(CASE WHEN result_of_report='accepted' THEN 1 END) = 0 is used to ensure that there is at least one row with result_of_report='accepted'. Note that the count function counts only not null values, so rows with result_of_report<>'accepted will not count.

    See a demo.

    Login or Signup to reply.
  2. 0

    We can use SUBSTRING_INDEX() along with aggregation here:

    SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(report_code, '-', 2), '-', -1) AS report_code
FROM yourTable
GROUP BY 1
HAVING SUM(result_of_report = 'accepted') = 0;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search