SQL MYSQL Abfrage von mehreren Merkmalen

[Schwenni]

Hallo,

ich habe eine Datenbank wo alle Merkmale eines Artikels in einer Tabelle über die Merkmals ID geschlüsselt drin sind.


zbsp für Artikel mit der ID 100, Farb ID 1, Hersteller ID 5, Kosten ID 25 schaut das dann so aus:

artikel_id - merkmal_id - value
100 - 1 - rot
100 - 5 - Walther
100 - 25 - 395

weitere Artikel haben dann nur Abweichende artikel_ID und Merkmalsausprägungen

Ich bräucht jetzt Hilfe zur SQL Abfrage wenn ich für jeden Artikel alle zugehörigen Merkmale auslesen möchte und diese dann Zeilenweise darstellen möchte.


Ergebnis:

100 - rot - Walther - 395
....
....


Ich steh da irgendwie auf dem Schlauch.

 

[DeusoftheWired]

SELECT * FROM name_deiner_tabelle WHERE artikel_id = "100"

 

[HominiLupus]

SELECT value FROM tabelle WHERE artikel_id = 100

Du bekommst dann natürlich mehr als eine Zeile

 

[Gohst]

Hast du also die Sachen in mehreren Tabellen? Also Farb ID 5 -> Farbtabelle dann z.B Rot usw?

ist zwar schon länger her, aber das sind doch die Joins die du machen müsstest oder halt ein etwas komplexeres Query dass die ID welche du dann erhälts in anderen Tabellen aufschlüsselt.

http://www.w3schools.com/sql/sql_join_inner.asp

 

[Mr_Bishop]

Ja würde auch sagen,
das klingt doch verdächtig nach Fremdschlüsseln auf andere Tabellen. Joins sollten also helfen.

Bei manchen Attributen aber könnte auch eine Enum dahinter stehen.

 

[Schwenni]

also mit artikel id = 100 in der where klausel hilft nicht weiter
ich möchte ja eine liste über alle artikel (mit der id 100 war ja nur als beispiel)

es sind quasi 3 Tabellen (Artikel, Merkmale, Merkmalwerte)
In der Artikel Tabelle ist zbsp Bescrheibung drin, in der Merkmaltabelle sind alle Merkmale definiert und in der Merkmalwertetabelle alle Werte zum Artikel zum Merkmal.

Merkmaltabelle
ID - Merkmal
1 - Farbe
5 - Hersteller
25 - Kosten

Merkmalwertetabelle
artikel_id - merkmal_id - value
100 - 1 - rot
100 - 5 - Walther
100 - 25 - 395
101 - 1 - schwarz
101 - 5- Heinz
101 - 25 - 40

Über letztere soll eine Abfrage laufen die mir folgende Ergebnistabelle liefert:

Für jeden Artikel eine Zeile (also gruppieren ???)

100 - rot - Walther - 395
101 - schwarz - Heinz - 40

 

[Gohst]

Schau dir die Joins mal an.
Du musst das ganze virtuell so in eine Tabelle Joinen damit das klappt.

Hättest du jetzt eine Programmiersprache wie PHP oder so darüber, könntest du scheibchenweise abfragen, was aber nicht so klug ist.

Dann könntest du alle mit aus Tabelle 1 mit ID xyz, dann dessen Atributte (sagen wir mal "Farbe", "Preis") abgreifen und in den jeweiligen Tabellen aufschlüsseln usw.

Kurz: mach ein Join dann ist das so als wärs in einer Tabelle.

 

[Balou72]

Stichworte: Joins und Sub-Selects.

Versuchs mal so (ungetestet):

SELECT a.id, m1.value, m2.value, m3.value FROM Artikel a 
LEFT JOIN (SELECT * FROM Merkmalwerte mw1 WHERE mw1.artikel_id = a.id AND mw1.merkmal_id=1) m1 
LEFT JOIN (SELECT * FROM Merkmalwerte mw1 WHERE mw1.artikel_id = a.id AND mw1.merkmal_id=5) m2 
LEFT JOIN (SELECT * FROM Merkmalwerte mw1 WHERE mw1.artikel_id = a.id AND mw1.merkmal_id=25) m3

Wenn Du die Merkmale nicht über ihre ID abfragen willst, sondern über ihren Namen, musst du sie auch noch in die Sub-Selects einarbeiten

 

[Yuuri]

Über letztere soll eine Abfrage laufen die mir folgende Ergebnistabelle liefert:

Überleg bitte erstmal was du genau willst. Du willst also eine Tabelle über die Werte, sagst aber eine Zeile pro Artikel. Bitte überleg vorher was du willst, anstatt hier irgendwas zu wollen, was du doch sowieso nicht willst. Gruppieren bringt dir hierbei gleich gar nichts, wenn du alle Werte zu Artikeln haben willst.

 

[kroto]

gibt es die Möglichkeit die Tabellen zu normalisieren? Also der Aufbau ist halt schon abstrus.

 

[ramtamtam]

gibt es die Möglichkeit die Tabellen zu normalisieren? Also der Aufbau ist halt schon abstrus.

danke, genau das dachte ich mir auch gerade... der Aufbau der Tabellen passt meiner Meinung nach gar nicht. eigentlich brauchst du, wenn es nicht noch mehr Daten und Tabellen gibt, nur eine Tabelle...

artikel id | Farbe | Hersteller | Kosten

Sollten oft die gleiche Farbe bzw. der gleiche Hersteller vorkommen, kannst du daraus extra Tabellen machen (bzw. würde ich das von Anfang an machen):

Artielstamm
artikel_id [PK] | farbe_id [FK] | hersteller_id [FK] | Kosten

Farben
farbe_id [PK] | Farbe

Hersteller
hersteller_id [PK] | Hersteller

Die passende Abfrage dafür wäre dann (ungetestet):

Select a.artikel_id, f.Farbe, h.Hersteller, a.Kosten
FROM Artikelstamm a
LEFT JOIN Farbe f ON a.farbe_id = f.farbe_id
LEFT JOIN Hersteller h ON a.hersteller_id = h.hersteller_id

 

[AgiOli]

Mit Gruppieren erreichst Du hier leider gar nichts.

Die einfachste, aber unschöne und unflexibelste Lösung wäre mit Subselects die Werte auslesen:

 select id
  ,(select value from merkmalvalues where artikel_id = a.id and merkmal_id = 1) as farbe
  ,(select value from merkmalvalues where artikel_id = a.id and merkmal_id = 5) as Hersteller
  ,(select value from merkmalvalues where artikel_id = a.id and merkmal_id = 25) as kosten
  from artikel a

Du hast aber spätestens ein Problem, wenn ein neues Merkmal dazu kommt oder Du bei verschiedenen Artikeln verschiedene Merkmale hast.
Ich habe was Ähnliches in SQL Server mal gemacht und dabei mit einer Kombination aus PIVOT und UNPIVOT gearbeitet. Was Dir in der Hinsicht MySQL bietet weiss ich nicht, aber google mal in Richtung Zeilen in Spalten ausgeben oder PIVOT/UNPIVOT mit MySQL

 

[Schwenni]

nein an der Datenbank ist nichts zu machen
es ist halt so

und zum gruppieren .... quasi wird gruppiert je Artikel ID
Deshalb je Artikel eine Zeile mit allen Merkmalen.

@Balou72

Deine Abfrage schaut mir vom Verständnis her passend aus, bekomme aber leider MYSQL Fehler wenn ich sie an meine Tabellennamen angepasst abfeuer.


#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'LIMIT 0, 30' at line 4

Mit einem Merkmal

select a.id, m1.value_text from artikel a
LEFT JOIN (SELECT * FROM dynamic_field_value mw1 WHERE mw1.object_id = a.id AND mw1.field_id = '54') m1

Ergänzung (7. Februar 2017)

@AgiOli
Ja Danke
Selbe Problem hier wohl auch.. zumal noch nicht mal alle Merkmale Pflichtfelder sind und somit auch garnicht existent in der Tabelle.
Habe Stand jetzt auch alle Merkmale einzeln abgefragt in Powerpivot und dann diese mittels der Artikel ID zusammengezogen.

 

[Enurian]

Einzig mögliche flexible Lösung ist eine stored procedure. Habe dir das mal von stackoverflow reinkopoert, bin gerade unterwegs. Damit kannst du es aber lösen.

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT ',' + QUOTENAME(ColumnName) 
                    from yourtable
                    group by ColumnName, id
                    order by id
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = N'SELECT ' + @cols + N' from 
             (
                select value, ColumnName
                from yourtable
            ) x
            pivot 
            (
                max(value)
                for ColumnName in (' + @cols + N')
            ) p '

exec sp_executesql @query;

 

[AgiOli]

Leute, der Aufbau ist überhaupt nicht abstrus und er ist normalisiert.

Ich habe eine Tabelle in der ich dynamisch festlegen kann, welche Merkmale ein Artikel haben soll. Somit habe ich nicht eine rießige Anzahl von allen nur möglichen Merkmalen in der Artikeltabelle und nur wenige sind gefüllt und alles ist unübersichtlich und jedes neue Merkmal bedeutet Programmierarbeit.

Ergänzung (7. Februar 2017)

Das Beispiel von Enurian funktioniert - aber nur auf MS-SQL-Server - nicht auf MySQL

 

[Schwenni]

Danke Enurian für deine Mühen aber ich hab wirklich nur MYSQL.

Danke nochmal an AgiOli und Balou72 - hatte nen Fehler - jetzt passt es.
Bei fehlenden Merkmalen gibt er halt NULL aus.

Klappt wunderbar mit den Subselects.
Danke Euch

PS: Jetzt hab ich es auch verstanden von der mysql Technik her *g*

 

[AgiOli]

Ich habe gerade nochmal geschaut - über dynamic SQL würde es wohl bei MySQL funktionieren.
Das Beispiel bei Stackoverflow ist deinem Problem sehr ähnlich. Schau's Dir mal an:
http://stackoverflow.com/questions/12598120/mysql-pivot-table-query-with-dynamic-columns

 

[Mirakel]

Der Aufbau deiner Tabelle ist für den Anwendungsfall jetzt eher unglücklich. Praktischer Wären mehrere Spalten in einer Tabelle (statt mehrer Zeilen pro Artikel), aber das geht auch "virtuell". Für andere Anwendungsfälle mag deine Organisation ja durchaus taugen.

Lösen wir mal kurz auf:
Du kannst die Mermals-Tabelle über einen Alias im Join einbinden.
Nehmen wir an, deine Artikel stehen in der Tabelle "Artikel" und die Tabelle mit Merkmalen heißt ArtMerkmale, dann sieht das so aus:

Select Artikel.id, Farbe.value, Hersteller.value, Kosten.value
From Artikel
LEFT JOIN ArtMerkmale AS Farbe ON Artikel.id = Farbe.artikel_id AND Farbe.merkmal_id = 1
LEFT JOIN ArtMerkmale AS Hersteller ON Artikel.id = Hersteller.artikel_id AND Hersteller.merkmal_id = 5
LEFT JOIN ArtMerkmale AS Kosten ON Artikel.id = Kosten.artikel_id AND Kosten.merkmal_id = 25

Damit referenzierst du für jedes Merkmal ein Subset der Merkmalstabelle, je nach Merkmal, dass du abfragen möchtest.
Gleichzeitig kannst du dann auf dieses Subset via Alias (z.B. "Hersteller") wie eine normale Tabelle zugreifen (mit dem Alias als Tabellennamen).

Ich kann leider keine Auskunft dazu geben, ob diese Lösung schnell oder langsam ist (hier nehme ich gerne Verbesserungsvorschläge entgegen), aber sie könnte funktionieren.

Falls es nicht klappt, schreibe doch mal deine Datenbankstruktur etwas genauer auf, dann können wir dir besser helfen.

 

[Schwenni]

Vielen Dank für deinen Ansatz
Funktioniert auch im Left Join wie in deinem Falle.
Werde mal alle 3 Varianten durchlaufen lassen und prüfen welche am schnellsten läuft und somit am performantesten ist.

Ergänzung (9. Februar 2017)

Variante 1 (subselects)
0.0058 Sek

Variante 2 (dynamisch)
0.0019 Sek

Variante 3 (left join)
0.0055 Sek


Alles über 158600 Datensätze.
Also zeitlich unterscheiden sich die Abfragen über die geringe Anzahl an Datensätzen nicht wirklich.
Der Aufbau der Tabelle im PHPMYADMIN dauerte bei Variante 3 am längsten.
Der Aufbau der Variante 2 ist dafür am schlankesten und übersichtlichsten, performant dazu und deshalb meine erste Wahl.

Danke an alle für die Hilfe und den geistigen Input!