Find Starting Point of mysql entries-CodePudding

I have a table in mysql which name track_questions, table have a following fields:

id: AUTO_INCREMENT
question_id: Foreign key question table
status: enum('entered','checked','verified','dissipated')
date: date

Unique: question_id and status combination

I have some record in table which not have "entered" status, and my start tracking point those question which have entered status. After deploy code some question are in admin and other pool so some other status entered without entry of starting point. I want to remove all question id which not have entered status. And if question_id has entered status then other status remain as it is.

CodePudding user response：

If I understand your question correctly you can use the following -

DELETE `tq1`
FROM `track_questions` `tq1`
LEFT JOIN `track_questions` `tq2`
    ON `tq1`.`question_id` = `tq2`.`question_id` AND `tq2`.`status` = 'entered'
WHERE tq2.id IS NULL;

This query DELETEs rows from the LEFT table (tq1) WHERE there is no match in the RIGHT table (tq1) joined on question_id and status = 'entered'.

If you want to better understand this you can run it as a SELECT statement first -

SELECT *
FROM `track_questions` `tq1`
LEFT JOIN `track_questions` `tq2`
    ON `tq1`.`question_id` = `tq2`.`question_id` AND `tq2`.`status` = 'entered'
WHERE `tq2`.`id` IS NULL;