[MySQL] JOIN Query mit besonderer Unterscheidung

strolch00 · 19 Dezember 2006

Hi @all,

folgendes Problem:

Ich habe drei Tabellen eine "ref", "ref_link", "ref_kat" wobei in referenz die Grundlegendsten Daten stehen, in der Tabelle "ref_link" steht die Verknüpfung der ref_id zur kat_id welche sich in der Tabelle "ref_kat" befindet. Der Query funktioniert schon soweit das er mir die Daten aus der Tabelle "ref" ausliest, allerding findet der irgendwie den bezug zu "ref_kat" nicht über die "ref_link".

Mein Query Versuch:

PHP:

	$_sql = "SELECT m.`nick`
				, r.`title`
				, r.`url`
				, r.`image`
				, r.`on_off`
				, k.`kat_description`
			FROM `".$Table['mem']."` AS m
			LEFT JOIN `".$Table['ref']."` AS r
				USING(`user_id`)
			LEFT JOIN `".$Table['ref_link']."` AS l
				USING(`ref_id`)
			LEFT JOIN `".$Table['ref_kat']."` AS k
				ON (l.`job` = k.`kat_id` OR l.`job_add` = k.`kat_id`)
			WHERE m.`nick`="._quote_smart($_GET['nick'])." AND r.`title` IS NOT NULL
			ORDER BY r.`title`";

Außerdem brauche ich eine Unterscheidung der kat_description einmal die Texte die in der Tabelle "ref_link" in der spalte job stehen und einmal die die aus job_add kommen. Ich hoff mal ich hab es so geschildert das Ihr es versteht.

Ich glaub ich denk zu kompliziert, und hoffe mal das einer den Denkfehler sieht.
*edit
Übrigens die Ausgabe von EXPLAIN schaut wie folgt aus

Code:

id 	select_type 	table 	type 	possible_keys 	key 	key_len 	ref 	rows 	Extra
1 	SIMPLE 	l 	system 	ref_id 	NULL 	NULL 	NULL 	0 	const row not found
1 	SIMPLE 	m 	ref 	Username 	Username 	300 	const 	1 	Using where; Using temporary; Using filesort
1 	SIMPLE 	r 	ref 	Profil 	Profil 	4 	usr_web1_1.m.user_id 	2 	Using where
1 	SIMPLE 	k 	ALL 	PRIMARY 	NULL 	NULL 	NULL 	10

**neues Problem*** siehe Post 11

ZeroCCC · 19 Dezember 2006

der query sieht richtig aus... hast du geprüft ob entspechende datsensätze existeren?

*edit* schau dir mal dein explain an... in tabelle l wird schon nix gefunden. somit findet er auch keine datensätze in ref_kat

strolch00 · 19 Dezember 2006

Oh bin ich ein A... hatte die Datensätz in der re_link vergessen. OK Der Query funktioniert soweit aber wie bekomme ich die Unterscheidung für job und job_add hin? Hast du eine Idee? Glaube kaum das AS in ON() funktioniert werde aber gleich mal nachschaun.

strolch00 · 20 Dezember 2006

Ich habe im Manual nix bezüglich AS in ON() gefunden also hab ich es getestet aber es funktioniert nicht.

*edit
Ja das hat mich auch stutzig gemacht war gerade am schaun welche die l-Tabelle war. Jetzt schauts schon besser aus

Code:

id 	select_type 	table 	type 	possible_keys 	key 	key_len 	ref 	rows 	Extra
1 	SIMPLE 	m 	ref 	Username 	Username 	300 	const 	1 	Using where; Using temporary; Using filesort
1 	SIMPLE 	r 	ref 	Profil 	Profil 	4 	usr_web1_1.m.user_id 	2 	Using where
1 	SIMPLE 	l 	ALL 	ref_id 	NULL 	NULL 	NULL 	3 	Using where
1 	SIMPLE 	k 	ALL 	PRIMARY 	NULL 	NULL 	NULL 	10

Aber ich finde keinen Hinweis darauf Wo Username und Profil herkommen, ich glaube die Spalten hiesen früher so vor dem Umbau.

strolch00 · 20 Dezember 2006

Wollte nur mal bescheitgeben bin mitlerweile soweit

PHP:

	$_sql = "SELECT m.`nick`
				, r.`title`
				, r.`url`
				, r.`image`
				, r.`on_off`
				, GROUP_CONCAT(k.`kat_description` ORDER BY k.`kat_description` DESC SEPARATOR ', ') AS `kat_description`
			FROM `".$Table['mem']."` AS m
			LEFT JOIN `".$Table['ref']."` AS r
				USING(`user_id`)
			INNER JOIN `".$Table['ref_link']."` AS l
				USING(`ref_id`)
			LEFT OUTER JOIN `".$Table['ref_kat']."` AS k
				ON l.`job` = k.`kat_id` OR l.`job_add` = k.`kat_id`
			WHERE m.`nick`="._quote_smart($_GET['nick'])." AND r.`title` IS NOT NULL
			GROUP BY r.`title` ORDER BY r.`title`";

allerdings bringt mir das auch nicht das ergebnis was ich will. Ich hoffe mal einer von euch kennt eine möglichkeit, subselect will ich nach möglichkeit vermeiden. Eine einfache AS Zuweisung wäre mir am liebsten. Aber ich bin am ende und weis nimmer weiter, im Manual finde ich auch nix weiter dazu.

*edit um es evtl einfacher zu machen (nur bekannte User):
Wer mir helfen will und die DB Tabellen selber will damit er testen kann und hier nicht immer erst posten will, schickt mir eine pm das sql kommt dann. Ist vielleicht einfacher so zu Handeln.

strolch00 · 20 Dezember 2006

Juhuu ich habs :biggrin:

PHP:

	$_sql = "SELECT m.`nick`
				, r.`title`
				, r.`url`
				, r.`image`
				, r.`on_off`
				, GROUP_CONCAT(k1.`kat_description` ORDER BY k1.`kat_description` DESC SEPARATOR '#') AS `job`
				, GROUP_CONCAT(k2.`kat_description` ORDER BY k2.`kat_description` DESC SEPARATOR '#') AS `job_add`
			FROM `".$Table['mem']."` AS m
			LEFT JOIN `".$Table['ref']."` AS r
				USING(`user_id`)
			INNER JOIN `".$Table['ref_link']."` AS l
				USING(`ref_id`)
			LEFT JOIN `".$Table['ref_kat']."` AS k1
				ON l.`job` = k1.`kat_id`
			LEFT JOIN `".$Table['ref_kat']."` AS k2
				ON l.`job_add` = k2.`kat_id`
			WHERE m.`nick`="._quote_smart($_GET['nick'])." AND r.`title` IS NOT NULL
			GROUP BY r.`title` ORDER BY r.`title`";

Jetzt ist nur noch zu klären ob der so sinnvoll ist.

Meine EXPLAIN schaut so aus:

und die Abfrage dauert 0.0032 sek. Kann man an den JOIN arten nochwas drehen? Oder die JOIN strucktur allgemein noch besser umstellen, z.B mit verscachtelungen(weis net genau obs so heist)? Wenn ich nur JOIN verwende um den Query optimizer zu nutzen kommt nix bei raus weil ich warscheinlich im letzten JOIN auf die vorletzte Tabelle zugreife. (Bin mir nicht sicher ob daran liegt)

ps mit verschachtelungen meine ich dies hier.

Und nochmal meine obere Frage wo kommen die rot Unterstrichenen Key´s her? Ich habe keine solchen Spalten, kann mir das mal einer erklären?

Johnson · 20 Dezember 2006

Wieso benutzst du einmal LEFT JOIN und einmal LEFT OUTER JOIN?

Kommt meines Wissens nach auf das Gleiche raus und die OUTER-Syntax wurde nur zur Kompatibilität zu Oracle eingeführt, oder liege ich da falsch?

Gruß

strolch00 · 20 Dezember 2006

Oh asso da muss ich nochmal lesen ich war der Meinung LEFT JOIN holt alle auch NULL spalten und LEFT OUTER JOIN bringt mir nur gefüllte Spalten ohne NULL.

Ich les nochmal fix.

*edit
Hast recht ich ändere es gleich. Aber mir drängt sich grad ne Frage auf:
Und zwar wieso sind die EXPLAIN Ausgaben anders wenn es eh nur ein Synonyme für LEFT JOIN ist?

Hier die Aktuelle:

Johnson · 20 Dezember 2006

strolch00 schrieb:
Oh asso da muss ich nochmal lesen ich war der Meinung LEFT JOIN holt alle auch NULL spalten und LEFT OUTER JOIN bringt mir nur gefüllte Spalten ohne NULL.

Meines Wissens nach holt ein LEFT JOIN auch Datensätze aus der ersten Tabelle, wenn in der zweiten kein entsprechender vorhanden ist, auf den ON() bzw. USING() zutrifft (die Datensätze beim Ergebnis aus der zweiten Tabelle werden dann entsprechend mit NULL aufgefüllt).

Ein INNER JOIN holt nur Datensätze, für die es sowohl in der ersten, als auch in der zweiten Tabelle einen passenden gibt.

strolch00 · 20 Dezember 2006

Johnson schrieb:
...

Ja das war ein Fehler meiner Seits. Bin grad am überlegen ob ich nicht den ersten JOIN auch auf INNER setz. Aber da glaube ich les ich mal noch bissi und warte mal auf eure Kommentare.

strolch00 · 22 Dezember 2006

Habe noch ein neues Problem. Ich habe folgende Tabelle:
id|ref_if|job|job_add|last_update
Nun will ich diese Daten in einem Array auslesen aber halt wieder nach Felder getrennt, Aber ich bekomme es einfach nicht hin, ich denke es liegt an der WHERE Bedingung.
Hier ist meine Query:

PHP:

$_sql = "SELECT l_one.`link_id` AS `job_id`
					, l_one.`job`
					, l_two.`link_id` AS `job_add_id`
					, l_two.`job_add`
				FROM `".$Table['ref_link']."` AS `l_one`
				INNER JOIN `".$Table['ref_link']."` AS `l_two`
				USING(`link_id`)
				WHERE (l_one.`ref_id` = ".(int)$res->ref_id." AND l_one.`job_add` = 0)
					OR (l_two.`ref_id` = ".(int)$res->ref_id." AND l_two.`job` = 0)";

Ich bekomme jedoch nicht die gewünschte Gruppierung sondern der liest mit in job_id und job_add_id alle id´s aus die er findet. Ist auch logisch durch die OR im WHERE aber AND kann ich vergessen weil es keinen Datensatz liefert. Nun Brauch ich ne idee wie ich das anstellen kann, das er die Gruppiert.
Ich befürchte fast ich denke wieder zu kompliziert bei diesem Query. GROUP BY und DISTINCT bringen keine erfolge.

Und dann noch vielleicht ein paar Kommentare zu diesem Query hier?

strolch00 schrieb:
PHP:

$_sql = "SELECT m.`nick` , r.`title` , r.`url` , r.`image` , r.`on_off` , GROUP_CONCAT(k1.`kat_description` ORDER BY k1.`kat_description` DESC SEPARATOR '#') AS `job` , GROUP_CONCAT(k2.`kat_description` ORDER BY k2.`kat_description` DESC SEPARATOR '#') AS `job_add` FROM `".$Table['mem']."` AS m LEFT JOIN `".$Table['ref']."` AS r USING(`user_id`) INNER JOIN `".$Table['ref_link']."` AS l USING(`ref_id`) LEFT JOIN `".$Table['ref_kat']."` AS k1 ON l.`job` = k1.`kat_id` LEFT JOIN `".$Table['ref_kat']."` AS k2 ON l.`job_add` = k2.`kat_id` WHERE m.`nick`="._quote_smart($_GET['nick'])." AND r.`title` IS NOT NULL GROUP BY r.`title` ORDER BY r.`title`";

Jetzt ist nur noch zu klären ob der so sinnvoll ist.

Meine EXPLAIN schaut so aus:

und die Abfrage dauert 0.0032 sek. Kann man an den JOIN arten nochwas drehen? Oder die JOIN strucktur allgemein noch besser umstellen, z.B mit verscachtelungen(weis net genau obs so heist)? Wenn ich nur JOIN verwende um den Query optimizer zu nutzen kommt nix bei raus weil ich warscheinlich im letzten JOIN auf die vorletzte Tabelle zugreife. (Bin mir nicht sicher ob daran liegt)

ps mit verschachtelungen meine ich dies hier.

Johnson · 22 Dezember 2006

Vielleicht schreibst du mal hier die Tabellenstruktur und das Wunschergebnis rein - in der Regel kann man aus einer Query, die falsche Ergebnisse liefert, nicht so toll das Wunschergebnis ableiten

Gruß

strolch00 · 22 Dezember 2006

Ich habe folgende Tabelle:

id|ref_if|job|job_add|last_update

Nun will ich diese Daten in einem Array auslesen aber halt wieder nach Felder getrennt.

Ich bekomme jedoch nicht die gewünschte Gruppierung sondern der liest mit in job_id und job_add_id alle id´s aus die er findet.

Aber extra nochmal für dich

Tabelle:

link_id | ref_id | job | job_add | last_update

gewünsche Ausgabe:

Code:

rray
(
    [0] => Array
        (
            [job_id] => 1
            [job] => 10
            [job_add_id] => 
            [job_add] => 
        )

    [1] => Array
        (
            [job_id] => 
            [job] => 
            [job_add_id] => 2
            [job_add] => 8
        )

    [2] => Array
        (
            [job_id] => 3
            [job] => 1
            [job_add_id] => 
            [job_add] => 
        )

    [3] => Array
        (
            [job_id] => 
            [job] => 
            [job_add_id] => 4
            [job_add] => 7
        )

)

zur Zeit ist dies die Ausgabe:

Code:

rray
(
    [0] => Array
        (
            [job_id] => 1
            [job] => 10
            [job_add_id] => 1
            [job_add] => 0
        )

    [1] => Array
        (
            [job_id] => 2
            [job] => 0
            [job_add_id] => 2
            [job_add] => 8
        )

    [2] => Array
        (
            [job_id] => 3
            [job] => 1
            [job_add_id] => 3
            [job_add] => 0
        )

    [3] => Array
        (
            [job_id] => 4
            [job] => 0
            [job_add_id] => 4
            [job_add] => 7
        )

)

[MySQL] JOIN Query mit besonderer Unterscheidung

strolch00

redraft.de

ZeroCCC

wasn das?

strolch00

redraft.de

strolch00

redraft.de

strolch00

redraft.de

strolch00

redraft.de

Johnson

Code-Frevler

strolch00

redraft.de

Johnson

Code-Frevler

strolch00

redraft.de

strolch00

redraft.de

Johnson

Code-Frevler

strolch00

redraft.de