Outer Join - Probleme

[gilzo]

Hallo,

ich habe da ein Problem, an dem ich nun schon den ganzen Abend rummache.

Es geht um folgendes:
Ich verwende in meiner MySQL-Datenbank unter anderen folgende 3 Tabellen:

USERS:
`User_ID` int(11) unsigned NOT NULL AUTO_INCREMENT,
`User_Surname` varchar(30) NOT NULL default '',
`User_Name` varchar(30) NOT NULL default '',
`User_Username` varchar(20) default NULL,
PRIMARY KEY (`Gilzo_User_ID`),
) TYPE=InnoDB ;

REPORT:
`Reports_ID` smallint(6) unsigned NOT NULL auto_increment,
`Reports_Name` varchar(30) default NULL,
PRIMARY KEY (`Gilzo_Reports_ID`)
) TYPE=InnoDB ;

ZWISCHENTABELLE_USER_REPORT:
`ZT_User_Reports_ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`ZT_User_Reports_UserID` int(11) unsigned NOT NULL default '0',
`ZT_User_Reports_ReportID` smallint(6) unsigned NOT NULL default '0',
PRIMARY KEY (`Gilzo_ZT_User_Reports_ID`)
) TYPE=InnoDB ;

Die Zwischentabelle habe ich deshalb, damit ich selbst zu einem Report mehrere Beiträge schreiben kann und auch mehrere Antworten zu einem Report erhalten kann.

Gefüllt Sie sie folgendermaßen: (Nur ein Auszug)

USERS:
1, Luca, Defeo, Luca
2, Sarah, Fraser, Sarah17
3, Steve, Carters, Stevy
4, Katrin, Postewa, Katy
...

REPORT:
1, Kanada
2, Nepal
3, Namibia
...

ZWISCHENTABELLE_USER_REPORT:
1, 1, 1
2, 1, 2
3, 2, 1
4, 3, 1

Es ist jetzt sehr einfach zu verstehen, daß Luca etwas mit Kanada und Nepal zu tun hat und Sarah und Steve etwas mit Kanada. Katrin hat mit keinem Bericht etwas zu tun.

Nun bin ich auf der Suche nach einer SQL-Abfrage, die mir die folgende Tabelle zurückgibt, wenn ich wissen will, wer von meinen Benutzern alles etwas mit Kanada zu tun hat:

User_ID, User_Surname, User_Name, User_Username, ZT_User_Reports_ReportID
1, Luca, Defeo, Luca, 1
2, Sarah, Fraser, Sarah17, 1
3, Steve, Carters, Stevy, 1
4, Katrin, Postewa, Katy, NULL

Ich hatte eigentlich gedacht, daß ich nun mit RIGHT OUTER JOIN arbeiten müsste, aber das klappt einfach irgendwie nicht.

Hier meine Abfrage:
SELECT User_ID, User_Surname, User_Name, User_Username, ZT_User_Reports_UserID, ZT_User_Reports_ReportID
FROM USERS
LEFT OUTER JOIN ZWISCHENTABELLE_USER_REPORT ON ZT_User_Reports_ReportID = 1

Das Ergebnis dieser Abfrage ist:
1, Luca, Defeo, Luca, 1
1, Luca, Defeo, Luca, 1
1, Luca, Defeo, Luca, 1
1, Luca, Defeo, Luca, 1
2, Sarah, Fraser, Sarah17, 1
2, Sarah, Fraser, Sarah17, 1
2, Sarah, Fraser, Sarah17, 1
2, Sarah, Fraser, Sarah17, 1
3, Steve, Carters, Stevy, 1
3, Steve, Carters, Stevy, 1
3, Steve, Carters, Stevy, 1
3, Steve, Carters, Stevy, 1
4, Katrin, Postewa, Katy, 1
4, Katrin, Postewa, Katy, 1
4, Katrin, Postewa, Katy, 1
4, Katrin, Postewa, Katy, 1

Und ich habe keine Ahnung warum... Normalerweise nimmt doch der Left Outer Join von der linken, also der USERS-Tabelle alle Felder und nur die von der rechten, für die es auch Einträge gibt.

Ich habe auch schon andere Abfragen probiert, aber leider alle nicht mit dem Ergebnis das ich will.

Ich hoffe, jemand kann mir hier weiterhelfen.
Vielen Dank schon mal im Voraus...

Viele Grüße und bis bald
Chiti

[mrhappiness]

Hinter dem "ON" steht, wie die beiden Tabellen miteinander verknüpft werden sollen.

Du solltest dort also auch aus jeder verwendeten Tabelle eine Spalte auflisten

ON users.user_id = zwischentabelle.ZT_User_Reports_UserID

Die Bedingung, dass es dir um Kanada geht, gehört in eine noch anzulegende WHERE-Klausel

[gilzo]

Hi mrhappiness,

vielen Dank für Deine schnelle Antwort.
Leider ist das nicht die gesucht Antwort. Das Ergebnis schaut dann wie folgt aus:

1, Luca, Defeo, Luca, 1
2, Sarah, Fraser, Sarah17, 1
3, Steve, Carters, Stevy, 1

Ist zwar schon einmal nicht schlecht, aber der 4. Eintrag fehlt eben, sobald ich mit einer WHERE-Klausel arbeite:
--> 4, Katrin, Postewa, Katy, NULL

Hier vielleicht noch kurz der SQL-Code, um die Tabellen mit den Einträgen zu erzeugen:

CREATE TABLE `users` (
`User_ID` int(11) unsigned NOT NULL AUTO_INCREMENT,
`User_Surname` varchar(30) NOT NULL default '',
`User_Name` varchar(30) NOT NULL default '',
`User_Username` varchar(20) default NULL,
PRIMARY KEY (`User_ID`)
) TYPE=InnoDB ;

INSERT INTO `Users` VALUES (1, 'Luca', 'Defeo', 'Luca');
INSERT INTO `Users` VALUES (2, 'Sarah', 'Fraser', 'Sarah17');
INSERT INTO `Users` VALUES (3, 'Steve', 'Carters', 'Stevy');
INSERT INTO `Users` VALUES (4, 'Katrin', 'Postewa', 'Katy');


CREATE TABLE `reports` (
`Reports_ID` smallint(6) unsigned NOT NULL auto_increment,
`Reports_Name` varchar(30) default NULL,
PRIMARY KEY (`Reports_ID`)
) TYPE=InnoDB ;

INSERT INTO `reports` VALUES (1, 'Kanada');
INSERT INTO `reports` VALUES (2, 'Nepal');
INSERT INTO `reports` VALUES (3, 'Namibia');

CREATE TABLE `zt_user_report` (
`ZT_User_Reports_ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`ZT_User_Reports_UserID` int(11) unsigned NOT NULL default '0',
`ZT_User_Reports_ReportID` smallint(6) unsigned NOT NULL default '0',
PRIMARY KEY (`ZT_User_Reports_ID`)
) TYPE=InnoDB ;

INSERT INTO `zt_user_report` VALUES (1, 1, 1);
INSERT INTO `zt_user_report` VALUES (2, 1, 2);
INSERT INTO `zt_user_report` VALUES (3, 2, 1);
INSERT INTO `zt_user_report` VALUES (4, 3, 1);

Hast Du sonst noch eine Idee, was ich falsch gemacht haben könnte???

Trotzdem einmal vielen Dank...
Viele Grüße
Chiti

[derHund]

Ist zwar schon einmal nicht schlecht, aber der 4. Eintrag fehlt eben, sobald ich mit einer WHERE-Klausel arbeite:
--> 4, Katrin, Postewa, Katy, NULL

Katy hat nichts mit Kanada zu tun, somit liefert Dir die Abfrage das gewünschte Ergebnis:

wer von meinen Benutzern alles etwas mit Kanada zu tun hat:

[gilzo]

Hallo,

vielleicht war meine Formulierung nicht ganz glücklich...

Nun bin ich auf der Suche nach einer SQL-Abfrage, die mir die folgende Tabelle zurückgibt, wenn ich wissen will, wer von meinen Benutzern alles etwas mit Kanada zu tun hat:

OK... dann berichtige ich es:
Ich will wissen, welcher User etwas mit Kanada zu tun hat und welcher nicht... oder anders formuliert: Ich hätte gerne eine Liste aller Benutzer und nur dann Einträge im Feld ZT_User_Reports_ReportID, wenn der jeweilige Benutzer auch etwas mit Kanada zu tun hat. Ansonsten eben NULL

User_ID, User_Surname, User_Name, User_Username, ZT_User_Reports_ReportID
1, Luca, Defeo, Luca, 1
2, Sarah, Fraser, Sarah17, 1
3, Steve, Carters, Stevy, 1
4, Katrin, Postewa, Katy, NULL

Ich hoffe, es ist nun eindeutig und irgendjemand kann mir hier weiterhelfen...

Vielen Dank
Chiti

[derHund]

Mit Deinen Testdaten liefert diese Query das gewünschte Ergebnis?

SELECT User_ID, User_Username, ZT_User_Reports_ReportID
FROM users u
LEFT JOIN zt_user_report ur
ON ur.ZT_User_Reports_UserID = u.User_ID AND ur.ZT_User_Reports_ReportID = 1

[gilzo]

hi...

na super... das war genau das, was ich gesucht habe...

Vielen lieben Dank

Chiti

PS: Dann kann es ja jetzt mit der WM losgehen...