SQL Table mit multiplen Einträgen joinen

[Blutschlumpf]

Ich habe folgendes Konstrukt:
Tabelle a) mit auto_increment Primärschlüssel
Tabelle b) mit variabler Anzahl Datensätzen die je den Primärschlüssel aus a und einen 2. Wert enthalten.

Ich möchte nun eine Abfrage auf Tabelle a erweitern die die Werte aus Tabelle 2 enthält.

Wenn ich das ganze mit LEFT JOIN mache bekomme ich das in etwa, aber mit dem Nebeneffekt dass ich wenn in Tabelle b 2 Werte zu einem Datensatz aus a drin sind, bekomme ich bei der Abfrage 2 Datensätze.
Das lässt sich schlecht verarbeiten und es gibt zum anderen das Problem dass man so auch nicht nach mehr als einem Wert aus Tabelle 2 filtern kann.
Gibt es evtl. eine Möglichkeit dass man das so joined dass da nachher eine Art Array rauskommt so dass jeder Datensatz genau eine Spalte in Tabelle a darstellt und die Werte aus Tabelle b als Aray in einem Feld der Abfrage landen ?

Mal als Beispiel

Tabelle a: produke
id        modell      preis
1         aaaaa       1234
2         bbbbb       1234
3         ccccc       1234
4         ddddd       1234
5         eeeee       1234

Tabelle b: farben
produktid  farbe
1          rot
2          rot
2          blau
5          gelb

Mit einem normalen LEFT JOIN bekomme ich:

id   modell     preis    farbe
1    aaaaa      1234     rot
2    bbbbb      1234     rot
2    bbbbb      1234     blau
3    ccccc      1234
4    ddddd      1234
5    eeeee      1234     gelb

Ich hätte aber gerne:

id   modell     preis    farbe
1    aaaaa      1234     rot
2    bbbbb      1234     rot,blau
3    ccccc      1234
4    ddddd      1234
5    eeeee      1234     gelb

Zudem würde ich gerne nach mehr als einem Wert unter Farbe filtern.

Umgebung ist MySQL 5 + PHP 5. Ihc könnte das zwar durch ne Abfrage auf a und dann nochmal in PHP auf Tabelle b lösen, das ganze wäre allerdings der Performance-Killer wenn da Tausende Abfragen stattfinden.

 

[Shader]

Ich würde dies mit einem ganz normalen Join machen:

Ich sage einfach:

SELECT a.*, b.*
FROM produkte AS a, farben AS b
WHERE a.id = b.id

Er müsste dir dann bezüglich des Produktes 2 zwei Zeilen anzeigen.
Willst du nach Farbe filtern oder so häng bei dem WHERE mit dem AND Operator einfach eine weitere Bedingung ran.
z.B. AND b.farbe LIKE 'rot'

 

[lararsc]

Habs noch nie ausprobiert aber mit group by id müsste es gehen wenn du die Farben über group_concat() zusammenfasst.

http://dev.mysql.com/doc/refman/5.1/en/group-by-functions.html

 

[Blutschlumpf]

Ich möchte ja genau nicht dass er mir 2 Zeilen ausgibt und ich möchte auch nach 2 Farben gleichzeitig suchen können.

Normales JOIN verträgt sich afaik auch nicht mit dem Rest der Query weil da zwangsläufig LEFT/RIGHT JOIN benutzt wird.

@lararsc: Guck ich mir mal an.

Edit: GROUP_CONCAT() scheint für die Anzeige zu funktionieren, allerdings kann man in den Werten die das ausgibt nicht mehr suchen.
Jemand noch ne Idee wie man das machen könnte ?

 

[lararsc]

wenn du effizient nach farbe suchen willst solltest du das dann natürlich vor dem group by und am besten auch schon vor dem join machen. ansonsten könntest du den zusammengesetzten String über eine regular expression mit LIKE noch auswerten.

 

[Blutschlumpf]

Wie mache ich das vor JOIN / GROUP BY ?
Wenn ich da einfach ein WHERE reinsetze was auf den output von GROUP_CONCAT zugreifen will, geht das soweit ich sehe nicht.
Selbst wenn ich nen subquery mache funktioniert das ja nicht weil ich nicht nach werten in 2 oder mehr Datensätzen suchen und die logisch verknüpfen kann, zumindest weiß ich nicht wie.

 

[lararsc]

ich meinte mit vorher halt noch im sql query wann genau im query was passiert entscheidet ja eh der interne optimierer. War etwas undeutlich formuliert...

Versuch doch einfach mal nicht nach dem concat Feld zu selektieren sondern nach Farben also where farbe = 'rot' oder so. Also das concat Feld nur für deine arrayartige Darstellung und den query auf den echten Daten.

 

[Blutschlumpf]

Das geht ja nicht bzw. das klappt ja nur richtig wenn ich nach genau einer Farbe/Eigenschaft suche.
Wenn ich mehrere Einträge für eine ID habe und ich suche mit AND-Verknüfpung kommt niemals ein Treffer, wenn ich mit OR suche reicht ein Eintrag der matched, ich kann also nicht nach mehr als einem Attribut suchen welches zutreffen muss.
Bei Farben ist das jetzt vieleicht ein blödes Beispiel, stell dir vor ich such ein Auto mit Allrad, Klimaanlage und Schiebedach.
Hier könnte man über den Umweg die Einträge zu zählen vielleicht noch ein reines AND-Szenario abbilden, aber kompliziertere Sachen (also z.B. hat Allrad, ist grün oder blau, VW oder Audi) nicht mehr.

 

[lararsc]

Also mal analog zu deinem auto beispiel:

Select * from auto join extras on auto.id = extras.auto_fk 
where auto.id in (
   SELECT auto.id FROM `auto` left join extras on auto.id = extras.auto_fk  
   WHERE (extras.name = "rot" or extras.name = "blau" or extras.name = "schwarz")
) 
and extras.name = "schiebedach"

 

[Blutschlumpf]

Hi,
danke, so ähnlich hab ichs jetzt auch gemacht, allerdings mach ich die logischen Verknüpfungen alle in der Haupt-Query und generiere für jede Eigenschaft ne eigene Sub-Query.
Das lässt sich einfacher per PHP generieren.

 

[roker002]

ich wusste garnicht dass man 2 Zeilen zusammen in eine spalte einzeigen lassen kann.

ich sehe da auch kein problem wenn du 2 Zeilen hast... dann musst du halt deine variablen nachher so zu verwaltung stellen dass es auch so klappt wie du willst!

 

[Blutschlumpf]

Das ganze in php so umzubauen dass das Programm mehrere rows als einen Datensatz auffasst ist doch deutlich umständlicher als das in MySQL zusammenzufassen und dann in php mit nem explode in ein Array umzuwandeln.

 

[roker002]

ja aber was du da willst wird glaube ich nicht von SQL servern unterstützt!

und so schlimm im code das zu behandeln ist es nicht...

 

[Blutschlumpf]

Das läuft doch schon lange (zumindest mit MySQL, für alles andere müsste man die Sachen eh umschreiben) und ohne mein Programm zu kennen bezweifle ich mal ganz stark dass du abschätzen kannst was da aufwendiger ist.