CODEWITHSUNDEEP
mysqlsql
ccdavies
ccdavies

Reputation: 1606

Check duplicate row doesn't already exist

Tables:

exp_submissions
-----------------------------------------------------------------------------------
id | entry_id | member_id | member_group | category_id | type_id | portfolio_number

exp_judging
------------------------------------------------
id | rel_id | judge_id | pre | stage_1 | stage_2

The aim:

The following query does everything up to the last part:

SELECT sub.entry_id 
FROM exp_judging AS jud
LEFT JOIN exp_submissions AS sub ON jud.rel_id = sub.id 
WHERE (jud.judge_id != 1 OR jud.judge_id IS NULL)
AND jud.pre = 1
AND sub.member_group = 5
LIMIT 1

These are the test rows I have in my exp_judging table:

enter image description here

As you will see, there is two rows with the rel_id = 35 (ID 200 and 197). The above query is selecting the row id 197 even though there is another row with the same rel_id and has a judge_id = 1. This is my issue.

I need to add to my query to check that no other row exists with the same rel_id and judge_id = 1, but I can't figure out how.

Thank you.

Upvotes: 0

Views: 54

Answers (1)

Oscar Pérez
Oscar Pérez

Reputation: 4397

You shoud use a NOT EXISTS clause:

   SELECT sub.entry_id 
     FROM exp_judging AS jud
LEFT JOIN exp_submissions AS sub ON jud.rel_id = sub.id 
    WHERE (jud.judge_id != 1 OR jud.judge_id IS NULL)
      AND jud.pre = 1
      AND sub.member_group = 5
      AND NOT EXISTS (SELECT sub2.entry_id 
                        FROM exp_judging AS jud2
                   LEFT JOIN exp_submissions AS sub2 ON jud2.rel_id = sub2.id 
                       WHERE (jud2.judge_id = 1 )
                         AND jud2.pre = 1
                         AND sub2.member_group = 5
                         AND jud2.rel_id = jud.rel_id)
    LIMIT 1

Edit: added a new condition

Upvotes: 1

Related Questions