LEFT Join mit bedingungen

[Andi79]

Ich finde einfach keine einfache Lösung bei einem left join wenn es 2 Bedingungen gibt

Gegeben ist

Tabelle A (kontakte)

mit

kontakt_id, kunde_id, ...

und Tabelle B mit

gruppe_id, kontakt_id

Eine Abfrage welche Kontakte in Tabelle A auch in Tabelle B vorhanden sind und in A die Kunde_id X haben und in B die Gruppe Y ist kein Problem.

Der umgekehrte Fall will mir leider nicht gelingen, wie mache ich die Abfrage (möglichst ohne Subselects) so das er mir alle anzeigte für die es in Tabelle B keine Zuordnung zur Gruppe Y gibt.

Weiß jemand rat?

 

[akretschmer]

zeige bitte die create table - statements und einige Demo-Daten, und was bei rauskommen soll, damit man das verständlich nachvollziehen kann.

 

[Andi79]

CREATE TABLE
gruppen_kontakte
(
gk_id bigint NOT NULL AUTO_INCREMENT,
gruppe_id INT,
kontakt_id INT,
PRIMARY KEY (gk_id)
)
ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE
kontakte
(
kontakt_id bigint NOT NULL AUTO_INCREMENT,
kunde_id INT,
kontakt_kuerzel VARCHAR(20),
PRIMARY KEY (kontakt_id)
)
ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO kontakte (kontakt_id, kunde_id, kontakt_kuerzel) VALUES (1, 1916, 'test1');
INSERT INTO kontakte (kontakt_id, kunde_id, kontakt_kuerzel) VALUES (2, 1916, 'test2');
INSERT INTO kontakte (kontakt_id, kunde_id, kontakt_kuerzel) VALUES (3, 1916, 'test3');

INSERT INTO gruppen_kontakte (gk_id, gruppe_id, kontakt_id) VALUES (1, 1, 2);
INSERT INTO gruppen_kontakte (gk_id, gruppe_id, kontakt_id) VALUES (1, 1, 3);

2 Abfragen werden gebraucht:

welche Kontakte des Kunde_id "1916" sind in der Tabelle gruppen_kontakte und haben dort die gruppe_id 1 und welche Kontakte sind nicht mit gruppe_id 1 in gruppen_kontakte (quasi die Umkehrabfrage).

abfrage 1 sollte also Test 1 und Test2 rausbringen, abfrage 2 den Test3

 

[akretschmer]

Dein zweites Insert in gruppen_kontakte sollte in richtigen Datenbanken einen Fehler bringen. Deine Angabe über das erste Ergebnis erscheint mir falsch. Und auch das zweite.

test=*# select * from kontakte;
 kontakt_id | kunde_id | kontakt_kuerzel
------------+----------+-----------------
  1 |  1916 | test1
  2 |  1916 | test2
  3 |  1916 | test3
(3 Zeilen)

test=*# select * from gruppen_kontakte;
 gk_id | gruppe_id | kontakt_id
-------+-----------+------------
  1 |  1 |  2
  2 |  1 |  3
(2 Zeilen)

test=*# select * from kontakte k inner join gruppen_kontakte gk on k.kontakt_id=gk.kontakt_id where kunde_id = 1916;
 kontakt_id | kunde_id | kontakt_kuerzel | gk_id | gruppe_id | kontakt_id
------------+----------+-----------------+-------+-----------+------------
  2 |  1916 | test2  |  1 |  1 |  2
  3 |  1916 | test3  |  2 |  1 |  3
(2 Zeilen)

test=*# select * from kontakte k left join gruppen_kontakte gk on k.kontakt_id=gk.kontakt_id where kunde_id = 1916 and gk_id is null;
 kontakt_id | kunde_id | kontakt_kuerzel | gk_id | gruppe_id | kontakt_id
------------+----------+-----------------+-------+-----------+------------
  1 |  1916 | test1  |  |  |   
(1 Zeile)

 

[Andi79]

Ja, sorry. war genau verdreht, test 2 und test3 muß in die erste gruppe, und test1 in die 2.

Was jetzt hier aber noch fehlt ist der where fall gruppe='1' bei beiden Abfragen. Bei der ersten bekomme ich das problemlos unter, bei der 2. klappt das so aber
nicht mehr.
select * from kontakte k inner join gruppen_kontakte gk on k.kontakt_id=gk.kontakt_id where kunde_id = 1916 and gruppe_id='1'; geht

select * from kontakte k left join gruppen_kontakte gk on k.kontakt_id=gk.kontakt_id where kunde_id = 1916 and gruppe_id='1' and gk_id is null; geht nicht mehr

Ohne diese Bedingung gibt er mir auch die Werte nicht zurück die zwar in der Tabelle gruppen_kontakte sind, aber einer anderen Gruppe

 

[akretschmer]

Was heißt 'geht nicht', und was erwartest Du bei "gk_id is null", wenn gk_id der PK ist?

 

[Andi79]

ein weiterer Insert um das zu verdeutlichen
INSERT INTO kontakte (kontakt_id, kunde_id, kontakt_kuerzel) VALUES (4, 1916, 'test4');
INSERT INTO gruppen_kontakte (gk_id, gruppe_id, kontakt_id) VALUES (1, 2, 4);

bei einem
select * from kontakte k left join gruppen_kontakte gk on k.kontakt_id=gk.kontakt_id where kunde_id = 1916 and gk_id is null;

bekomme ich diesen Satz nicht zurück da gk_id für diese kontakt_id logischerweise nicht null ist. Die Abfrage soll aber alle kontakt_ids mit der Kunde_id 1916 zurückgeben die in der tabelle gruppe_kontakte
nicht der gruppe 1 zugeordnet ist oder gar keiner gruppe zugeordnet ist). Die Ausgabe der Umkehrfunktion sollte test1 und test4 sein. Test1 ist nirgends zugeordnet, test4 nur der gruppe_id 2, in gruppe_id 1 also noch verfügbar.

 

[akretschmer]

der Insert in gruppen_kontakte scheitert bereits, weil (gk_id)=(1) bereits vorhanden ist.

INSERT INTO gruppen_kontakte (gk_id, gruppe_id, kontakt_id) VALUES (3, 2, 4);
test=*# select * from kontakte k left join gruppen_kontakte gk on k.kontakt_id=gk.kontakt_id where kunde_id = 1916 and gk_id is null or gruppe_id != 1;
 kontakt_id | kunde_id | kontakt_kuerzel | gk_id | gruppe_id | kontakt_id
------------+----------+-----------------+-------+-----------+------------
  4 |  1916 | test4  |  3 |  2 |  4
  1 |  1916 | test1  |  |  |   
(2 Zeilen)

 

[Andi79]

ok, so ungefähr klappt es. danke.