Tabelle zweimal JOIN-nen?

Hallo NG!

Folgendes Problemchen beschäftigt mich zur Zeit:
Ich habe eine Tabelle "Produkte", in der eben jede Menge
Produkte 'drinstehen. Jedes Produkt kann einen oder zwei
Hersteller haben. Die Daten zu den Herstellern an sich stehen in
der Tabelle "Hersteller".
Die Verknüpfung wollte ich nun so gestalten, daß ich in die
Tabelle "Produkte" die beiden Felder "Hersteller1" und
"Hersteller2" einbaue und darin auf die ID-Nummer des jeweiligen
Herstellers verweise.
Allerdings scheitert das daran, daß ich die Hersteller-Tabelle
nicht zweimal per "LEFT JOIN" in eine DB-Abfrage einbinden kann
(einmal auf Produkte.Hersteller1 und einmal auf
Produkte.Hersteller2).

Lange Rede, kurzer Sinn: Wie kann man das Problem (eleganter)
lösen?


TIA,

Sebastian

--
http://www.baumaschinen-modelle.net
http://www.schwerlast-rhein-main.de

Re: Tabelle zweimal JOIN-nen?

Sebastian Suchanek schrieb:

> Allerdings scheitert das daran, daß ich die Hersteller-Tabelle
> nicht zweimal per "LEFT JOIN" in eine DB-Abfrage einbinden kann

SELECT * FROM Produkte p
LEFT JOIN Hersteller h1 on p.Hersteller1 = h1.ID
LEFT JOIN Hersteller h2 on p.Hersteller2 = h2.ID

Gruß. Claus

Re: Tabelle zweimal JOIN-nen?

Sebastian Suchanek wrote:
> Allerdings scheitert das daran, daß ich die Hersteller-Tabelle
> nicht zweimal per "LEFT JOIN" in eine DB-Abfrage einbinden kann
> (einmal auf Produkte.Hersteller1 und einmal auf
> Produkte.Hersteller2).

Die Antwort hast Du ja schon bekommen. Falls Du die Moeglichkeit
in Betracht ziehst, dass die Anzahl Hersteller doch auch mal
groesser als 2 werden kann, empfiehlt sich eine Hilfstabelle
der Art

create table hergestellt_von
(
integer produkt_id,
integer hersteller_id
);

so dass Du dann schreiben kannst

select * from produkte p, hersteller h, hergestellt_von hv
where hv.produkt_id = p.id and hv.hersteller_id = h.id

Bye
Frederik

--
Frederik Ramm ## eMail frederik@remote.org ## N49°00.22' E008°24.56'

Re: Tabelle zweimal JOIN-nen?

Thus spoke Frederik Ramm:

> Sebastian Suchanek wrote:
>> Allerdings scheitert das daran, daß ich die
>> Hersteller-Tabelle nicht zweimal per "LEFT JOIN" in eine
>> DB-Abfrage einbinden kann (einmal auf Produkte.Hersteller1
>> und einmal auf Produkte.Hersteller2).
>
> Die Antwort hast Du ja schon bekommen. Falls Du die
> Moeglichkeit in Betracht ziehst, dass die Anzahl Hersteller
> doch auch mal groesser als 2 werden kann, empfiehlt sich
> eine Hilfstabelle der Art
> [...]

Die Möglichkeit einer n:m-Verknüpfungstabelle kenne ich und
überlege gerade, sie nicht doch einzusetzen, *aber*...

> select * from produkte p, hersteller h, hergestellt_von hv
> where hv.produkt_id = p.id and hv.hersteller_id = h.id

....mit dieser Art der Abfrage erhalte ich im Ergebnis "doppelte"
Zeilen in dem Sinn, daß ein Produkt mit zwei Herstellern in zwei
Zeilen auftaucht. Einmal mit dem ersten Hersteller und einmal
mit dem zweiten Hersteller.

Da "zwei Hersteller" in meinem Fall aber nicht bedeutet, daß
Hersteller 1 *oder* Hersteller 2 das Produkt herstellen, sondern
Hersteller 1 *und* 2 *zusammen*, benötige ich evtl.
"Mehrfachhersteller" in einer Zeile. Gibt's dazu auch eine
MySQL-Lösung, oder bleibt da nur "Applikations-Ebenen-
Gefrickel"?


Tschüs,

Sebastian

--
http://www.baumaschinen-modelle.net
http://www.schwerlast-rhein-main.de

Re: Tabelle zweimal JOIN-nen?

Sebastian Suchanek macht die Ingrid:

Auch auf die Gefahr hin, quengelig zu wirken:

> [...]
> benötige ich evtl. "Mehrfachhersteller" in einer Zeile.
> Gibt's dazu auch eine MySQL-Lösung, oder bleibt da nur
> "Applikations-Ebenen- Gefrickel"?

Gibt's da wirklich keine gute Lösung?


Tschüs,

Sebastian

--
http://www.baumaschinen-modelle.net
http://www.schwerlast-rhein-main.de

Re: Tabelle zweimal JOIN-nen?

Sebastian Suchanek wrote:

> Die Möglichkeit einer n:m-Verknüpfungstabelle kenne ich und
> überlege gerade, sie nicht doch einzusetzen, *aber*...
>
>> select * from produkte p, hersteller h, hergestellt_von hv
>> where hv.produkt_id = p.id and hv.hersteller_id = h.id
>
> ...mit dieser Art der Abfrage erhalte ich im Ergebnis "doppelte"
> Zeilen in dem Sinn, daß ein Produkt mit zwei Herstellern in zwei
> Zeilen auftaucht.

Schau Dir mal in diesem Zusammenhang de Funktion GROUP_CONCAT an. Wenn
ich Dich richtig verstehe, willst Du damit eine Zusammenfassung aller
einem Produkt zugeordneten Hersteller.

create temporary table prod (
id int unsigned auto_increment primary key,
name varchar(20)
);
create temporary table herst (
id int unsigned auto_increment primary key,
name varchar(20)
);
create temporary table prodherst (prod int unsigned, herst int unsigned);

insert into prod (name) values ('Nagelfeile'),('Schnitzel'),('Blume');
insert into herst (name) values
('Sandmann'),('Blechmeier'),('Fleischerei'),('Mutter Natur');
insert into prodherst (prod,herst) values (1,1),(1,2),(2,3),(2,4),(3,4);


select prod.name Produktname, group_concat(herst.name) Hersteller FROM
prod JOIN herst JOIN prodherst ON prod.id=prod AND herst.id=herst GROUP
BY prod.id;
+-------------+--------------------------+
| Produktname | Hersteller |
+-------------+--------------------------+
| Nagelfeile | Sandmann,Blechmeier |
| Schnitzel | Fleischerei,Mutter Natur |
| Blume | Mutter Natur |
+-------------+--------------------------+
3 rows in set (0,02 sec)

> Da "zwei Hersteller" in meinem Fall aber nicht bedeutet, daß
> Hersteller 1 *oder* Hersteller 2 das Produkt herstellen, sondern
> Hersteller 1 *und* 2 *zusammen*, benötige ich evtl.
> "Mehrfachhersteller" in einer Zeile. Gibt's dazu auch eine
> MySQL-Lösung, oder bleibt da nur "Applikations-Ebenen-
> Gefrickel"?

Die obige Ausgabe korrekt auszuwerten ("diese beiden sind Hersteller"),
ist dann wohl Applikationssache.


Oder hab ich Dich jetzt völlig falsch verstanden?
--
[X] <-- nail here for new monitor

Re: Tabelle zweimal JOIN-nen?

Sebastian Suchanek macht erneut die Ingrid:

> [...]
> Da "zwei Hersteller" in meinem Fall aber nicht bedeutet,
> daß Hersteller 1 *oder* Hersteller 2 das Produkt
> herstellen, sondern Hersteller 1 *und* 2 *zusammen*,
> benötige ich evtl. "Mehrfachhersteller" in einer Zeile.
> Gibt's dazu auch eine MySQL-Lösung, oder bleibt da nur
> "Applikations-Ebenen- Gefrickel"?

Inzwischen ist mir selbst noch eine Lösung eingefallen, die
ich Euch nicht vorenthalten möchte. Zunächst die
Datenstruktur:

CREATE TABLE produkte (
id int(10) unsigned auto_increment,
name varchar(255)
...
) COMMENT = 'Alle Produktdaten'

CREATE TABLE hersteller (
id int(10) unsigned auto_increment,
name varchar(255)
...
) COMMENT = 'Alle Herstellerdaten'

CREATE TABLE verknuepfung (
id int(10) unsigned auto_increment,
produkt int(10) unsigned,
hersteller int(10) unsigned,
gewichtung int(3) unsigned
) COMMENT = 'Verknüpfung zwischen Produkten und Herstellern'

Die Abfrage sieht dann so aus:

SELECT p.name AS produkt, h1.name AS h1, h2.name AS h2...
FROM produkte p
LEFT JOIN verknuepfung v1 ON v1.produkt = p.id AND v1.gewichtung = 1
LEFT JOIN verknuepfung v2 ON v2.produkt = p.id AND v2.gewichtung = 2
...
LEFT JOIN hersteller h1 ON h1.id = v1.hersteller
LEFT JOIN hersteller h2 ON h2.id = v2.hersteller
...
ORDER BY h1, h2

Die Lösung hat den Vorteil, daß man in der
Verknüfungs-Tabelle auch gleich eine Gewichtung der einzelnen
Hersteller vornehmen kann, was z.B. bei der sinnvollen
Sortierung helfen kann.

Dummerweise hat diese Lösung auch zwei gravierende Nachteile:
1. Da MySQL (noch) keine Constraints beherrscht, kann die
Datenbank nicht von sich aus die Datenintegrität dahingehend
sicherstellen, daß pro Produkt z.B. nicht zweimal die selbe
Gewichtung vergeben wird oder daß z.B. als Gewichtung nur 1
und 3 vergeben wird. (Was wiederrum bestenfalls zu unschönen
Ergebnissen führen würde.)
2. Man muß vor der Abfrage separat (z.B. im umgebenden
PHP-Skript) bestimmen, was die größte Anzahl von
Hersteller an einem beliebigen Produkt ist:

SELECT COUNT(manuf) AS anzahl FROM test GROUP BY model ORDER BY anzahl DESC LIMIT 1

Mit Hilfe dieses Wertes und Schleifendurchläufen müssen
dann die o.g. Spaltennamen sowie LEFT-JOIN-Statements in
passender Anzahl erstellt werden.


Fazit für mich persönlich: Da bei meinem Ausgangsproblem
realistisch kaum mehr als zwei Hersteller pro Produkt
auftreten werden, bleibe ich bei meiner bisherigen Lösungen,
zwei Herstellerfelder in der Produkttabelle zu haben.
Die formal bessere Normalisierung wiegt IMHO die Nachteile
und den größeren Aufwand bei der Nutzung nicht auf - im
Gegenteil. YMMV.


Tschüs,

Sebastian

--
http://www.baumaschinen-modelle.net
http://www.schwerlast-rhein-main.de

Re: Tabelle zweimal JOIN-nen?

Thus spoke Thomas Rachel:

> [...]
> Schau Dir mal in diesem Zusammenhang de Funktion
> GROUP_CONCAT an.
> [...]
> Oder hab ich Dich jetzt völlig falsch verstanden?

Du hast mich vollkommen richtig verstanden, danke. :-)

Das sieht insgesamt auch ziemlich gut aus und wenn ich das
GROUP_CONCAT-Statement noch ein wenig gemäß Doku modifiziere,
komme ich möglichweise sogar mit weniger "PHP-Gezappel" aus. :-)


Tschüs,

Sebastian

--
http://www.baumaschinen-modelle.net
http://www.schwerlast-rhein-main.de

Re: Tabelle zweimal JOIN-nen?

Sebastian Suchanek wrote:
> Sebastian Suchanek macht erneut die Ingrid:
>
> Inzwischen ist mir selbst noch eine Lösung eingefallen, die
> ich Euch nicht vorenthalten möchte. Zunächst die
> Datenstruktur:
>
> CREATE TABLE produkte (
> id int(10) unsigned auto_increment,
> name varchar(255)
> ...
> ) COMMENT = 'Alle Produktdaten'
>
> CREATE TABLE hersteller (
> id int(10) unsigned auto_increment,
> name varchar(255)
> ...
> ) COMMENT = 'Alle Herstellerdaten'
>
> CREATE TABLE verknuepfung (
> id int(10) unsigned auto_increment,
> produkt int(10) unsigned,
> hersteller int(10) unsigned,
> gewichtung int(3) unsigned
> ) COMMENT = 'Verknüpfung zwischen Produkten und Herstellern'

Soweit OK, typische N:M Relation

> Die Abfrage sieht dann so aus:
>
> SELECT p.name AS produkt, h1.name AS h1, h2.name AS h2...
> FROM produkte p
> LEFT JOIN verknuepfung v1 ON v1.produkt = p.id AND v1.gewichtung = 1
> LEFT JOIN verknuepfung v2 ON v2.produkt = p.id AND v2.gewichtung = 2
> ...
> LEFT JOIN hersteller h1 ON h1.id = v1.hersteller
> LEFT JOIN hersteller h2 ON h2.id = v2.hersteller
> ...
> ORDER BY h1, h2

Schreck laß nach!

SELECT ...
FROM produkte AS p
INNER JOIN verknüpfung AS v ON (p.id = v.produkt)
INNER JOIN hersteller AS h ON (h.id = v.hersteller)
ORDER BY p.id,
v.gewichtung DESC

Und weil die Daten jetzt ordentlich normiert sind, ist es das Ergebnis
auch. Für ein Produkt mit nur einem Hersteller bekommst du eine Zeile,
für ein Produkt mit 3 Herstellern drei.

> Dummerweise hat diese Lösung auch zwei gravierende Nachteile:

> 1. Da MySQL (noch) keine Constraints beherrscht, kann die
> Datenbank nicht von sich aus die Datenintegrität dahingehend
> sicherstellen, daß pro Produkt z.B. nicht zweimal die selbe
> Gewichtung vergeben wird oder daß z.B. als Gewichtung nur 1
> und 3 vergeben wird. (Was wiederrum bestenfalls zu unschönen
> Ergebnissen führen würde.)

Woot?

ALTER TABLE verknüpfung ADD UNIQUE INDEX (produkt, gewichtung)
-> pro Produkt jede Gewichtung nur einmal

Und wenn du als Gewichte nur 1 und 3 haben willst, nimm ein ENUM.

> 2. Man muß vor der Abfrage separat (z.B. im umgebenden
> PHP-Skript) bestimmen, was die größte Anzahl von
> Hersteller an einem beliebigen Produkt ist ...

Genau deswegen macht man es *nicht* so. Der einzige Teil an SQL-
Statements, der von Daten abhängig ist, sollten Bedingungen nach
WHERE oder HAVING sein.

> Fazit für mich persönlich: Da bei meinem Ausgangsproblem
> realistisch kaum mehr als zwei Hersteller pro Produkt
> auftreten werden, bleibe ich bei meiner bisherigen Lösungen,
> zwei Herstellerfelder in der Produkttabelle zu haben.

D.h. deine Abbildung der Realität ist fehlerhaft. Eine "Lösung"
würde ich das nicht nennen.

> Die formal bessere Normalisierung wiegt IMHO die Nachteile
> und den größeren Aufwand bei der Nutzung nicht auf - im
> Gegenteil. YMMV.

MMV definitiv. Eventuell willst du das ja nochmal bewerten,
nachdem du es *richtig* gemacht hast.


XL

Re: Tabelle zweimal JOIN-nen?

Axel Schwenke schrieb:

> [...]
> SELECT ...
> FROM produkte AS p
> INNER JOIN verknüpfung AS v ON (p.id = v.produkt)
> INNER JOIN hersteller AS h ON (h.id = v.hersteller)
> ORDER BY p.id,
> v.gewichtung DESC
>
> Und weil die Daten jetzt ordentlich normiert sind, ist es das Ergebnis
> auch. Für ein Produkt mit nur einem Hersteller bekommst du eine Zeile,
> für ein Produkt mit 3 Herstellern drei.
> [...]

Du hast aber schon gelesen und verstanden, daß ich genau das (3 Zeilen)
nicht will?

> Eventuell willst du das ja nochmal bewerten, nachdem du es *richtig*
> gemacht hast.

Mache ich. Indem ich Thomas' Vorschlag verwende und nicht Deinen.


Tschüs,

Sebastian

Re: Tabelle zweimal JOIN-nen?

Thus spoke Thomas Rachel:
> Sebastian Suchanek wrote:
>
> [...]
> Schau Dir mal in diesem Zusammenhang de Funktion
> GROUP_CONCAT an.
> [...]

Eine Frage hätte dazu allerdings doch noch: Gibt es einen Weg,
mit MySQL-Mitteln die reine Anzahl von Treffern herauszufinden?

"COUNT($prod-Spalte)" ohne "GROUP BY prod.id" liefert einen
falschen Wert, weil dabei Mehrfachhersteller auch mehrfach
gezählt werden. Und mit "GROUP BY prod.id" erhalte ich eine
Liste aller Ergebnisse, wobei in der COUNT-Spalte die Anzahl der
Hersteller abgebildet wird.
Einfach auf JOIN & GROUP_CONCAT verzichten geht auch nicht, weil
u.U. eine WHERE-Bedingung auf just die betreffenden Felder
zugreift/zutrifft.


TIA,

Sebastian

--
http://www.baumaschinen-modelle.net
http://www.schwerlast-rhein-main.de

Re: Tabelle zweimal JOIN-nen?

Sebastian Suchanek wrote:

> Thus spoke Thomas Rachel:
>> Sebastian Suchanek wrote:
>>
>> [...]
>> Schau Dir mal in diesem Zusammenhang de Funktion
>> GROUP_CONCAT an.
>> [...]
>
> Eine Frage hätte dazu allerdings doch noch: Gibt es einen Weg,
> mit MySQL-Mitteln die reine Anzahl von Treffern herauszufinden?

Du könntest das Ganze in einen Subquery kapseln (ab 4.1.irgendwas) oder
in eine temporäre Tabelle schreiben.

Außenrum sollte COUNT() dann arbeiten.


Thomas
--
Wo kaemen wir den da hin, wenn jeder jedes Fup auf einen eigenen Artikel
gleich persoenlich nehmen wuerden... Oh... nach dang... :)
(Christian Wederhake in de.alt.admin)

Re: Tabelle zweimal JOIN-nen?

Sebastian Suchanek wrote:
> Axel Schwenke schrieb:
>
>> SELECT ...
>> FROM produkte AS p
>> INNER JOIN verknüpfung AS v ON (p.id = v.produkt)
>> INNER JOIN hersteller AS h ON (h.id = v.hersteller)
>> ORDER BY p.id,
>> v.gewichtung DESC
>>
>> Und weil die Daten jetzt ordentlich normiert sind, ist es das Ergebnis
>> auch. Für ein Produkt mit nur einem Hersteller bekommst du eine Zeile,
>> für ein Produkt mit 3 Herstellern drei.
>> [...]
>
> Du hast aber schon gelesen und verstanden, daß ich genau das (3 Zeilen)
> nicht will?

Jetzt ja.

Du hast nicht geschrieben, *warum* du das nicht willst und mir fällt
gerade kein *guter* Grund ein.

Natürlich kannst du einzelne Spalten des Ergebnisses gruppenweise per
GROUP_CONCAT zusammenfassen. Allerdings verlierst - bzw. verdeckst -
du dabei Information (im Beispiel die Verbindung von Hersteller und
Gewicht). Das was GROUP_CONCAT macht, kannst du genausogut in der
Anwendung erledigen, wenn du die Daten z.B. zur Anzeige aufbereitest.


XL

Re: Tabelle zweimal JOIN-nen?

Thus spoke Axel Schwenke:
> Sebastian Suchanek wrote:
>> Axel Schwenke schrieb:
>>
>>> SELECT ...
>>> FROM produkte AS p
>>> INNER JOIN verknüpfung AS v ON (p.id = v.produkt)
>>> INNER JOIN hersteller AS h ON (h.id = v.hersteller)
>>> ORDER BY p.id,
>>> v.gewichtung DESC
>>>
>>> Und weil die Daten jetzt ordentlich normiert sind, ist es
>>> das Ergebnis auch. Für ein Produkt mit nur einem
>>> Hersteller bekommst du eine Zeile, für ein Produkt mit 3
>>> Herstellern drei. [...]
>>
>> Du hast aber schon gelesen und verstanden, daß ich genau
>> das (3 Zeilen) nicht will?
>
> Jetzt ja.
>
> Du hast nicht geschrieben, *warum* du das nicht willst und
> mir fällt gerade kein *guter* Grund ein.

Weil das Ergebnis, das ganz am Ende aus PHP 'rausfällt, im
Prinzip genau aussehen soll wie die Beispielausgabe in


> Natürlich kannst du einzelne Spalten des Ergebnisses
> gruppenweise per GROUP_CONCAT zusammenfassen. Allerdings
> verlierst - bzw. verdeckst - du dabei Information (im
> Beispiel die Verbindung von Hersteller und Gewicht).

Das ist zum Zeitpunkt der Ausgabe bzw. für den Betrachter auch
nicht mehr relevant. Das ist genau genommen nur eine Krücke, um
sicherzustellen, daß Auflistung der Einzelhersteller zu einem
Produkt in der von mir gewünschten Reihenfolge stattfindet.

> Das
> was GROUP_CONCAT macht, kannst du genausogut in der
> Anwendung erledigen, wenn du die Daten z.B. zur Anzeige
> aufbereitest.

/Könnte/ man, ja. Allerdings ist das IMHO ein ziemlich
unelegantes Gewurschtel und führt überdies zu Problemen, wenn
man die Trefferanzahl von Suchen/Abfragen bestimmen will.


Tschüs,

Sebastian

--
http://www.baumaschinen-modelle.net
http://www.schwerlast-rhein-main.de

Re: Tabelle zweimal JOIN-nen?

Thus spoke Thomas Rachel:

> [...]
> Du könntest das Ganze in einen Subquery kapseln
> [...]

*patsch* Der Wald und die Bäume... :-)

Ach ja, JFTR: Da ich die GROUP_CONCAT-Geschichte zweimal
brauche (einmal für die Hersteller des Vorbilds und einmal
für die Hersteller des Modells), habe ich das GROUP_CONCAT-
Teil-Statement noch um ein DISTINCT ergänzen müssen.


Danke,

Sebastian

--
http://www.baumaschinen-modelle.net
http://www.schwerlast-rhein-main.de