[Erledigt] Left join und inner join

stefanjann · 16.10.2008, 12:16

Hallo,

ich habe drei Tabellen

person: ident, name
kategorie: ident, kat
personzukat: zuperson, zukat

Ich habe eine Suche geschrieben, die mit alle Personen gibt, die eine Kategroie haben die das Wort "eins" enthalten.

Code:

SELECT *
  FROM person p
  INNER JOIN personzukat z
    ON z.zuperson=p.ident
  INNER JOIN kategorie k
    ON k.ident=z.zukat
  WHERE LOWER(k.kat) LIKE '%eins%';

Das geht.
Jetzt brauche ich noch die Überprüfung, dass er nur Leute anzeigen soll, die keine Kategorie haben, die "zwei" in einer Kategroei stehen haben. Mein Versuch war folgt:

Code:

SELECT *
  FROM person p
  INNER JOIN personzukat z1
    ON z1.zuperson=p.ident
  INNER JOIN kategorie k1
    ON k1.ident=z1.zukat
  LEFT JOIN personzukat z2
    ON z2.zuperson=p.ident
  LEFT JOIN kategorie k2
    ON (k2.ident=z2.zukat) AND (k2.kat LIKE '%zwei%')
  WHERE (
      (LOWER(k2.kat) LIKE '%eins%')
    AND ((k2.kat NOT LIKE '%zwei%') OR ISNULL(k2.ident))
    )

Komischerweise ist die Tabelle k2 immer NULL.
Wenn ich die Tabellen z2 und k2 mit INNER JOIN verknüpfe (und den Teil in der WHERE weglasse) erlange ich treffer. Also müsste doch bei LEFT JOIN auch ein Treffer kommen, oder?
Wo liegt mein Denkfehler?

Danke schon mal.

stefanjann · 16.10.2008, 12:19

PS: MySQL 4.1.13-Max

agrajag · 16.10.2008, 13:02

So auf den ersten Blick würde ich sagen:
In der WHERE-Bedingung muss es k1.kat LIKE '%eins%' lauten und das hier: (k2.kat NOT LIKE '%zwei%') solltest du weglassen (da du ja schon in der ON-Bedingung k2.kat LIKE ... sehen hast)

Also ungefähr so:

Code:

SELECT 
    *
FROM
    person p
JOIN
    personzukat z1
ON
    z1.zuperson = p.ident
JOIN
    kategorie k1
ON
    k1.ident = z1.zukat
JOIN
    personzukat z2
ON
    z2.zuperson = p.ident
LEFT JOIN
    kategorie k2
ON
    k2.ident=z2.zukat AND k2.kat LIKE '%zwei%'
WHERE
    LOWER(k2.kat) LIKE '%eins'
    AND
    k2.ident IS NULL

Die vielen LIKE '%..%' abfragen können dir aber noch das Genick brechen, da so kein Inde genutzt werden kann. Wozu muss das LIKE sein?

stefanjann · 16.10.2008, 13:25

Es ist eine Suche und leider sind die Suchbegriffe immer nur Teil der Kategorie. Daher muss ich wohl mit LIKE suchen.

Deinen Weg hab ich schon versucht, der klappt leider nicht aus dem folgenden Grund:
Hans // Kat eins, zwei, drei
Peter // Kat eins, zwei, vier
Gerald // Kat eins, drei, vier

Suche alle die Kat 1 haben aber nicht Kat 3
Es Erscheinen Hans, Peter und Gerald mit dem Ergebnis
Hans // k1: eins // k2: zwei
Peter // K1: eins // k2: vier
Gerald // K1: eins // k2: vier

Obwohl ich ja Hans und Gerald über Kat drei auschließen will.

Ich hab mir da verschiedene Toturials und FAQs angesehen. Die sind sich alle Einig. Per JOIN LEFT die Kategorie binden und per WHERE nach ISNULL(k1) OR (k2<>'drei') ausschließen.

Zitat:

k1.kat LIKE '%eins%'

Äh, ja... Das hab ich beim Übertrag aus meinem Quellcode falsch abgetippt. Bei mir sinds soch etwas mehr Felder in der DB die mit abgefragt werden und ich wollte den Code hier aufs wesentliche reduzieren.

agrajag · 16.10.2008, 13:36

Dein Ergebnis kann nicht das Ergebnis meines Vorschlags sein - schließlich wird mit

Code:

ON
    k2.ident=z2.zukat AND k2.kat LIKE '%drei%'

nur auf k2-Sätze gejoint die eine "drei" enthalten.

k2.kat muss also eine "drei" enthalten oder NULL falls es keinen k2-Eintrag gibt, der eine "drei" enthält. Und mit dem WHERE k2.ident IS NULL filtern wir dann genau die Datensätze raus, bei denen dort eine NULL steht...

stefanjann · 16.10.2008, 13:45

Zitat:

Zitat von agrajag

Dein Ergebnis kann nicht das Ergebnis meines Vorschlags sein - schließlich wird mit

Code:

ON
    k2.ident=z2.zukat AND k2.kat LIKE '%drei%'

nur auf k2-Sätze gejoint die eine "drei" enthalten.

k2.kat muss also eine "drei" enthalten oder NULL falls es keinen k2-Eintrag gibt, der eine "drei" enthält. Und mit dem WHERE k2.ident IS NULL filtern wir dann genau die Datensätze raus, bei denen dort eine NULL steht...

Stimmt, ich hatte einen Tippfehler meinerseits drinnen (OR statt AND geschrieben). Bei mir ist jetzt (wie in meinem ersten Beitrag) k2.kat immer NULL. Ich dreh gleich durch, weil ich doch in k2.kat einen Treffer haben müsste.

Hans // k1: eins // k2: NULL
Peter // K1: eins // k2: NULL
Gerald // K1: eins // k2: NULL

stefanjann · 16.10.2008, 14:41

Ok, irgendwie drehen wir uns hier immer in Kreis. Jetzt mal ordentliche Strukturdaten zum nachtesten:

Code:

    
CREATE TABLE IF NOT EXISTS `kategorie` (
  `ident` bigint(20) unsigned NOT NULL auto_increment,
  `kat` varchar(25) collate utf8_bin NOT NULL default '',
  PRIMARY KEY  (`ident`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=6 ;

INSERT INTO `kategorie` (`ident`, `kat`) VALUES
(1, 'Frauenchor'),
(2, 'Kinderchor'),
(3, 'Jugendchor'),
(4, 'Jugendorchester'),
(5, 'Jugendblaskapelle');

CREATE TABLE IF NOT EXISTS `person` (
  `ident` bigint(20) unsigned NOT NULL auto_increment,
  `person` varchar(50) collate utf8_bin NOT NULL default '',
  PRIMARY KEY  (`ident`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=7 ;

INSERT INTO `person` (`ident`, `person`) VALUES
(1, 'Hans'),
(2, 'Franz'),
(3, 'Sepp'),
(4, 'Gerald'),
(5, 'Markus');

CREATE TABLE IF NOT EXISTS `personzukat` (
  `zuperson` bigint(20) unsigned NOT NULL default '0',
  `zukat` bigint(20) unsigned NOT NULL default '0',
  KEY `zuperson` (`zuperson`,`zukat`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

INSERT INTO `personzukat` (`zuperson`, `zukat`) VALUES
(1, 3),
(1, 4),
(2, 4),
(2, 5),
(3, 2),
(4, 3),
(4, 5),
(5, 2),
(5, 4),
(5, 5);

Die Anforderung ist: Gib mir alle die in irgendwas mit "jugend" sind aber kein "chor" sind:

Code:

SELECT *
  FROM person p
  INNER JOIN personzukat z1
    ON z1.zuperson = p.ident
  INNER JOIN kategorie k1
    ON k1.ident = z1.zukat
  INNER JOIN personzukat z2
    ON z2.zuperson = p.ident
  LEFT JOIN kategorie k2
    ON k2.ident=z2.zukat AND k2.kat LIKE '%chor%'
  WHERE LOWER(k1.kat) LIKE '%jugend%'
    AND k2.ident IS NULL

Ergebnis:

Code:

ident | person | zuperson | zukat | ident | kat               | zuperson | zukat | ident | kat
1     | Hans   | 1        | 3     | 3     | Jugendchor        | 1        | 4     | NULL  | NULL
1     | Hans   | 1        | 4     | 4     | Jugendorchester   | 1        | 4     | NULL  | NULL
2     | Franz  | 2        | 4     | 4     | Jugendorchester   | 2        | 4     | NULL  | NULL
2     | Franz  | 2        | 4     | 4     | Jugendorchester   | 2        | 5     | NULL  | NULL
2     | Franz  | 2        | 5     | 5     | Jugendblaskapelle | 2        | 4     | NULL  | NULL
2     | Franz  | 2        | 5     | 5     | Jugendblaskapelle | 2        | 5     | NULL  | NULL
4     | Gerald | 4        | 3     | 3     | Jugendchor        | 4        | 5     | NULL  | NULL
4     | Gerald | 4        | 5     | 5     | Jugendblaskapelle | 4        | 5     | NULL  | NULL
5     | Markus | 5        | 4     | 4     | Jugendorchester   | 5        | 4     | NULL  | NULL
5     | Markus | 5        | 4     | 4     | Jugendorchester   | 5        | 5     | NULL  | NULL
5     | Markus | 5        | 5     | 5     | Jugendblaskapelle | 5        | 4     | NULL  | NULL
5     | Markus | 5        | 5     | 5     | Jugendblaskapelle | 5        | 5     | NULL  | NULL

Finden sollte er nur: Franz! Denn alle anderen haben eine Kategorie mit %chor%.

stefanjann · 16.10.2008, 15:45

Ja ne nä is klar nä? Wenn man sich das Ergebnis mal anschaut, und evtl mal die Zeile "AND k2.ident IS NULL" auskommentiert, siehst man, dass der Fehler durch die Findung mehrerer Kategorien passiert. Somit ist meein toller Ansatz Mist. Mit Hilfe von SubQuerys konnte ich das Problem jetzt lösen:

Code:

SELECT *,
  (SELECT k2.kat FROM personzukat z2, kategorie k2
    WHERE ((z2.zukat=k2.ident) AND (z2.zuperson=p.ident))
      AND (k2.kat LIKE '%chor%') LIMIT 1
  ) AS isbad
  FROM person p
  INNER JOIN personzukat z
    ON z.zuperson = p.ident
  INNER JOIN kategorie k1
    ON k1.ident = z.zukat
  WHERE LOWER(k1.kat) LIKE '%jugend%'
  HAVING (ISNULL(isbad));

Vielleicht gibt es noch einen eleganteren Weg. Wenn ja, dann bitte posten. Ansonsten danke für die Hilfe.

Themen-Optionen
Druckbare Version zeigen Jemanden per E-Mail auf dieses Thema hinweisen
Thema bewerten
Thema bewerten:

Ähnliche Themen
Thema	Autor	Forum	Antworten	Letzter Beitrag
[Erledigt] LEFT JOIN verliert Datensätze	JudGer	Datenbanken	4	05.10.2008 17:53
[Erledigt] Inner Join Problembei der Ausgabe	evochriss	PHP Tipps 2008	1	26.08.2008 21:35
WHERE-Clause im Left Join	cycap	Datenbanken	2	06.11.2007 11:06
JOIN / LEFT JOIN und Co	prinzli	Datenbanken	12	05.11.2007 22:15
[solved] LEFT JOIN - #1066 - Not unique table/alias	moose	Datenbanken	6	12.12.2006 06:41
Problem mit Inner join	Jabi	Datenbanken	15	09.05.2006 14:10
Left outer join, timestamps	php-newbie83	Datenbanken	29	27.04.2006 09:29
INNER JOIN + Suchkriterien + Abfrage	duerov	PHP Tipps 2006	4	04.04.2006 12:47
inner join zeigt gleiche spalten nur 1x		Datenbanken	3	22.02.2006 17:11
inner join aus db		PHP Tipps 2006	8	22.01.2006 01:23
Select Problem		Datenbanken	17	16.01.2006 21:54
[Erledigt] Abfrageproblem mit Inner Join		Datenbanken	15	29.11.2005 18:17
[Erledigt] LEFT JOIN funtkioniert nicht richtig		Datenbanken	3	19.10.2005 17:38
[Erledigt] left outer join mit bedingung		Datenbanken	2	04.03.2005 00:52
den letzte(grösten) eintrag aus LEFT JOIN		Datenbanken	2	24.07.2004 18:33

16.10.2008, 12:16
stefanjann Erfahrener Benutzer Registriert seit: 02.08.2005 Beiträge: 252 PHP-Kenntnisse: Fortgeschritten	[Erledigt] Left join und inner join Hallo, ich habe drei Tabellen person: ident, name kategorie: ident, kat personzukat: zuperson, zukat Ich habe eine Suche geschrieben, die mit alle Personen gibt, die eine Kategroie haben die das Wort "eins" enthalten. Code: SELECT * FROM person p INNER JOIN personzukat z ON z.zuperson=p.ident INNER JOIN kategorie k ON k.ident=z.zukat WHERE LOWER(k.kat) LIKE '%eins%'; Das geht. Jetzt brauche ich noch die Überprüfung, dass er nur Leute anzeigen soll, die keine Kategorie haben, die "zwei" in einer Kategroei stehen haben. Mein Versuch war folgt: Code: SELECT * FROM person p INNER JOIN personzukat z1 ON z1.zuperson=p.ident INNER JOIN kategorie k1 ON k1.ident=z1.zukat LEFT JOIN personzukat z2 ON z2.zuperson=p.ident LEFT JOIN kategorie k2 ON (k2.ident=z2.zukat) AND (k2.kat LIKE '%zwei%') WHERE ( (LOWER(k2.kat) LIKE '%eins%') AND ((k2.kat NOT LIKE '%zwei%') OR ISNULL(k2.ident)) ) Komischerweise ist die Tabelle k2 immer NULL. Wenn ich die Tabellen z2 und k2 mit INNER JOIN verknüpfe (und den Teil in der WHERE weglasse) erlange ich treffer. Also müsste doch bei LEFT JOIN auch ein Treffer kommen, oder? Wo liegt mein Denkfehler? Danke schon mal. __________________ SELECT * benutze ich nur um den Post kurz zu halten.


Mister Ad PHP Code Flüsterer Registriert seit: 21.08.2005 Beiträge: 4682 PHP-Kenntnisse: Fortgeschritten

16.10.2008, 12:19
stefanjann Erfahrener Benutzer Registriert seit: 02.08.2005 Beiträge: 252 PHP-Kenntnisse: Fortgeschritten	PS: MySQL 4.1.13-Max __________________ SELECT * benutze ich nur um den Post kurz zu halten.

16.10.2008, 13:02
agrajag Moderator Registriert seit: 02.10.2006 Beiträge: 3.820 PHP-Kenntnisse: Fortgeschritten	So auf den ersten Blick würde ich sagen: In der WHERE-Bedingung muss es k1.kat LIKE '%eins%' lauten und das hier: (k2.kat NOT LIKE '%zwei%') solltest du weglassen (da du ja schon in der ON-Bedingung k2.kat LIKE ... sehen hast) Also ungefähr so: Code: SELECT * FROM person p JOIN personzukat z1 ON z1.zuperson = p.ident JOIN kategorie k1 ON k1.ident = z1.zukat JOIN personzukat z2 ON z2.zuperson = p.ident LEFT JOIN kategorie k2 ON k2.ident=z2.zukat AND k2.kat LIKE '%zwei%' WHERE LOWER(k2.kat) LIKE '%eins' AND k2.ident IS NULL Die vielen LIKE '%..%' abfragen können dir aber noch das Genick brechen, da so kein Inde genutzt werden kann. Wozu muss das LIKE sein? __________________ Today you...Tomorrow me.

16.10.2008, 13:36
agrajag Moderator Registriert seit: 02.10.2006 Beiträge: 3.820 PHP-Kenntnisse: Fortgeschritten	Dein Ergebnis kann nicht das Ergebnis meines Vorschlags sein - schließlich wird mit Code: ON k2.ident=z2.zukat AND k2.kat LIKE '%drei%' nur auf k2-Sätze gejoint die eine "drei" enthalten. k2.kat muss also eine "drei" enthalten oder NULL falls es keinen k2-Eintrag gibt, der eine "drei" enthält. Und mit dem WHERE k2.ident IS NULL filtern wir dann genau die Datensätze raus, bei denen dort eine NULL steht... __________________ Today you...Tomorrow me.

16.10.2008, 15:45
stefanjann Erfahrener Benutzer Registriert seit: 02.08.2005 Beiträge: 252 PHP-Kenntnisse: Fortgeschritten	Lösung Ja ne nä is klar nä? Wenn man sich das Ergebnis mal anschaut, und evtl mal die Zeile "AND k2.ident IS NULL" auskommentiert, siehst man, dass der Fehler durch die Findung mehrerer Kategorien passiert. Somit ist meein toller Ansatz Mist. Mit Hilfe von SubQuerys konnte ich das Problem jetzt lösen: Code: SELECT , (SELECT k2.kat FROM personzukat z2, kategorie k2 WHERE ((z2.zukat=k2.ident) AND (z2.zuperson=p.ident)) AND (k2.kat LIKE '%chor%') LIMIT 1 ) AS isbad FROM person p INNER JOIN personzukat z ON z.zuperson = p.ident INNER JOIN kategorie k1 ON k1.ident = z.zukat WHERE LOWER(k1.kat) LIKE '%jugend%' HAVING (ISNULL(isbad)); Vielleicht gibt es noch einen eleganteren Weg. Wenn ja, dann bitte posten. Ansonsten danke für die Hilfe. __________________ SELECT benutze ich nur um den Post kurz zu halten.