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

MySQL Query unique field but only when there is no double entry

Hi im not a native speaker and im not into sql language. I can do some standard queries like select * from table… update and so on but have no expierience in deeper sql questions.

plz ort gemeinde
1000 uttenthal buchkirchen
1000 buchkirchen buchkirchen
2000 mistelbach buchkirchen
2000 unterholz buchkirchen

I have a table like this and need to get a list of plz where ort <> gemeinde but only when there is no case where ort=gemeinde for this plz.

In this example i only should get plz 2000 because there exists a entry where ort buchkirchen = gemeinde buchkirchen for plz 1000.

Tags:

2

Answers


  1. 0

    You can use Not Exists

    select plz from table t1 
where not exists
(select 1 from table t2 where t1.plz = t2.plz and ort = gemenide)
    Login or Signup to reply.
  2. 0

    You can achieve this by using GROUP BY and HAVING:

      SELECT plz
    FROM table
    GROUP BY plz
    HAVING COUNT(CASE WHEN ort = gemeinde THEN 1 END) = 0
       AND COUNT(CASE WHEN ort <> gemeinde THEN 1 END) > 0;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search