Spalten und Zeilen aus zwei Tabellen als View/Tabelle vereinen

[Maggi]

Hallo,
ich bin neu hier und habe folgende Frage.
Ich möchte aus zwei Tabellen einen View oder eine neue Tabelle erzeugen und dabei Spalten aus der ersten Tabelle mit Zeilen aus der zweiten Tabelle in einer Zeile für jede ID vereinen.
Dazu habe ich auch schon im Netz folgendes gefunden als Beispiel:
SELECT
jomkas_users. name, email,
MAX(CASE WHEN profile_key = "profile.postal_code" THEN profile_value END) AS PLZ,
MAX(CASE WHEN profile_key = "profile.city" THEN profile_value END) AS Stadt,
MAX(CASE WHEN profile_key = "profile.dob" THEN profile_value END) AS Geburtstag
FROM jomkas_users LEFT JOIN jomkas_user_profiles ON jomkas_users.id = jomkas_user_profiles.user_id
GROUP BY jomkas_users.id

Dabei wird aus der Tabelle jomkas.users die Spalte Name und Email vereint mit den Werten der Zeilen profile.postal_code, profile.city und profile.dob. Das funktioniert auch und es wird eine Tabelle erstellt die dann die Spalten: Name, email, PLZ, Stadt und Geburtstag enthält.

Was noch nicht funktioniert ist:
1. In den neuen Spalten PLZ, Stadt und Geburtstag stehen alle Werte oder auch Leere Felder in Anführungszeichen.
2. Das Datum hat das Format YYYYMMDD bei der Eingabe sollte aber in der Tabelle in dem Deutsche Format angezeigt werden : DDMMYYYY
3. Die Tabelle jomkas_users ist verknüpft mit einer weiteren Tabelle (jomkas_user_usergroup_map), welche die Spalten user_id und group_id enthält. Alle User werden hier unter User_id aufgelistet (entsprechend der IDs aus der "jomkas_User" Tabelle). Es sollen nur Uder mit dem Wert "9" in der Spalte group_id in die zu bildende Tabelle übernommen werden.

Ich habe den Code in ein Modul des CMS Joomla (ArtTable) eingegeben und daraus wird dann eine Tabelle generiert.

Unabhängig davon habe ich die identische Fragestellung (nur mit anderen Tabellen) nochmal:
Also eine Tabelle (kasjos_joomgallery.) aus der zwei Spalten ausgelesen werden (imgtitle, imgauthor,)
Daraus soll dann ein View bzw. Tabelle gebildet werden zusammen mit den Werten der Zeilen aus einer zweiten Tabelle (kasjos_joomgallery_image_details). Beide Tabellen sind verknüpft über die Ids (kasjos_joomgallery.id = kasjos_joomgallery_image_details.id). In der Tabelle "kasjos_joomgallery_image_details" sind in der Spalte "details_key " mehrere Zeilen die zu einer ID aus der ersten Tabelle gehören dann die Werte "details_value" die ausgelesen werden sollen:
details_key, details_value
additional.Dv_Nr, Wert A
additional.Streifen_max, Wert B
usw.
Am Ende soll auch hier ein View oder Tablle entstehen:
imgtitle, imgauthor, Wert A, Wert B usw.

Hat jemand eine Idee....
Vielen Dank

 

[BerndB]

Hi,

versuchs mal

die Zeile:
MAX(CASE WHEN profile_key = "profile.city" THEN profile_value END) AS Stadt,

durch diese aus
GROUP_CONCAT(if(profile_key ='profile.city', profile_value, NULL)) AS 'Stadt'

Gruss

Bernd

 

[BerndB]

am Ende meiner Zeile fehlt ein Komma (sorry)

 

[Maggi]

Hallo,
vielen Dank, wenn ich es so ab ändere bekomme ich eine Fehlermeldung:
1 SELECT
2 jomkas_users. name, email,
3 GROUP_CONCAT(if(profile_key ='profile.postal_code', profile_value, NULL)) AS 'PLZ',
4 GROUP_CONCAT(if(profile_key ='profile.city', profile_value, NULL)) AS 'Stadt',
5 GROUP_CONCAT(if(profile_key ='profile.dob', profile_value, NULL)) AS 'Geburtstag',
6 FROM jomkas_users LEFT JOIN jomkas_user_profiles ON jomkas_users.id = jomkas_user_profiles.user_id
7 GROUP BY jomkas_users.id

#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 'FROM jomkas_users LEFT JOIN jomkas_user_profiles ON jomkas_users.id = jomkas_use' at line 6

Viele grüße
Maggi

 

[BerndB]

Zeil 4 ist am Ende ein Komma zuviel.

Gruss

Bernd

 

[BerndB]

Das war ja gar nix.

Am ENDE der Zeile 5 muss das Komma weg :-(

 

[Maggi]

Hi,
stimmt sorry jetzt ist das Kommata am Ende der Zeile 5 weg, dann geht es wieder ohne Fehler, aber nun stehen in den leeren (nicht ausgefüllten Feldern) Null und die Gänsefüße sind aber immer noch da bei Feldern die werte enthalten.... :-(
Grüße
Maggi

 

[BerndB]

Tausch bei mir NULL noch durch einen leerstring aus also 2x Gänsefüße ''

Gruss

Bernd

 

[Maggi]

Hallo,
also nun ist es so dass bei den Feldern mit Werten (also Einträgen) dies erscheint:
,,,,,,,,"60437",,,,,"Frankfurt",,,,,,,,,,,,"1978-01-20",,,,,


Wenn nichts im Feld steht ist es leer.
Ich habe in der Tabelle jomkas_user_profiles nachgeschaut und gesehen, dass dort in jedem Feld auch schon die Gänsefüße enthalten sind, egal ob ein Eintrag vorhanden ist ode nicht (dann um den Eintrag herum). Anscheinend werden diese schon von dem Plugin aus Joomla eingefügt? Obwohl ich dort nichts eingetragen habe.
Grüße
Maggi

 

[akretschmer]

Hallo,
ich bin neu hier und habe folgende Frage.
Ich möchte aus zwei Tabellen einen View oder eine neue Tabelle erzeugen und dabei Spalten aus der ersten Tabelle mit Zeilen aus der zweiten Tabelle in einer Zeile für jede ID vereinen.
Dazu habe ich auch schon im Netz folgendes gefunden als Beispiel:
SELECT
jomkas_users. name, email,
MAX(CASE WHEN profile_key = "profile.postal_code" THEN profile_value END) AS PLZ,
MAX(CASE WHEN profile_key = "profile.city" THEN profile_value END) AS Stadt,
MAX(CASE WHEN profile_key = "profile.dob" THEN profile_value END) AS Geburtstag
FROM jomkas_users LEFT JOIN jomkas_user_profiles ON jomkas_users.id = jomkas_user_profiles.user_id
GROUP BY jomkas_users.id

Dabei wird aus der Tabelle jomkas.users die Spalte Name und Email vereint mit den Werten der Zeilen profile.postal_code, profile.city und profile.dob. Das funktioniert auch und es wird eine Tabelle erstellt die dann die Spalten: Name, email, PLZ, Stadt und Geburtstag enthält.

Was noch nicht funktioniert ist:
1. In den neuen Spalten PLZ, Stadt und Geburtstag stehen alle Werte oder auch Leere Felder in Anführungszeichen.
2. Das Datum hat das Format YYYYMMDD bei der Eingabe sollte aber in der Tabelle in dem Deutsche Format angezeigt werden : DDMMYYYY
3. Die Tabelle jomkas_users ist verknüpft mit einer weiteren Tabelle (jomkas_user_usergroup_map), welche die Spalten user_id und group_id enthält. Alle User werden hier unter User_id aufgelistet (entsprechend der IDs aus der "jomkas_User" Tabelle). Es sollen nur Uder mit dem Wert "9" in der Spalte group_id in die zu bildende Tabelle übernommen werden.

Vielen Dank

Hier ist eigentlich mehr falsch, als man in den wenigen Zeilen falsch machen kann.

• mehr Spalten, als in Aggregation / Gruppierung genannt. Hint: alle Spalten im Resulat entweder aggregieren oder gruppieren. Keine Spalte ohne Aggregation oder Gruppierung
• was passiert, wenn eines der CASE WHEN nicht erfüllt ist?
• max() auf eine PLZ, das ist logisch gesehen Bullshit
• max() auf eine Stadt, siehe oben
• offenbar erlaubt Dein DB-Modell je User mehrere Geburtstage, siehe oben

In Summe: eigentlich solltest Du uns jetzt Dein Datenmodell mal zeigen. Aber ich denke, das ist so schlecht, daß es besser ist, Du wirfst es weg ohne es zu zeigen.

 

[BerndB]

Der nächste Versuch

GROUP_CONCAT(if(profile_key ='profile.city', profile_value, '')) AS 'Stadt',

durch

GROUP_CONCAT(if(profile_key ='profile.city', TRIM( '"' FROM profile_value) , '')) AS 'Stadt',

ersetzen, wobei die 4 '"' = Gänsefuß Anführungszeichen Gänsefuß sind

Also das/die Zeichen die du löschen willst in Gänsefüße

Gruss

Bernd

 

[Maggi]

Hallo akretschmer,
ich bedanke mich auch für diesen Beitrag, da er Anfangs noch sehr sachlich meine tatsächlich vorhandene Unkenntnis beschreibt, allerdings zum Ende für mich etwas unverständlich wurde.
Was ist mit "Dein Datenmodell" gemeint? Und wenn ich es wegwerfe was soll ich dann zeigen um Hilfe zu bekommen?
VG
Maggi

 

[akretschmer]

Hallo akretschmer,
ich bedanke mich auch für diesen Beitrag, da er Anfangs noch sehr sachlich meine tatsächlich vorhandene Unkenntnis beschreibt, allerdings zum Ende für mich etwas unverständlich wurde.
Was ist mit "Dein Datenmodell" gemeint? Und wenn ich es wegwerfe was soll ich dann zeigen um Hilfe zu bekommen?
VG
Maggi

Datenmodell: Das, was Du machen willst, und das, was Du an Tabellen definiert hast. Weder das eine noch das andere ist aus Deinem Posting ersichtlich.

 

[Maggi]

Hallo akretschmer,
wenn es nicht aus meinem ersten Posting hervor gegangen sein sollte, dann Entschuldigung. Auch wenn ich dachte dies geschrieben zu haben.
Es handelt sich um zwei Tabellen:
1. jomkas.users und daraus die Spalten id, name und email.
In der zweiten Tabelle
2. jomkas_user_profiles mit den Spalten user_id, profile_key, profile_value.
Die Id aus der ersten ist mit der User_id aus der zweiten Tabelle verknüpft.
Für jede id aus der ersten Tabelle stehen in der zweiten Tabelle in jeder Zeile dann verschiedene Einträge in der Spalte profile_key.
Beispiel:
User_id, profile_key, profile_value
41, profile.postal_code, 60437
41, profile.city, Frankfurt
41, profile.dob, 1968-02-15
43, profile.postal_code, 60435
43, profile.city, Frankfurt
43, profiledob, 1970-08-25
usw.
Die Spalten sind durch Kommata getrennt hier im Forum.
Am Ende soll eine Tabelle mit den Spalten:
name, email, PLZ, Stadt, Geburtstag
herauskommen für jede id pro Zeile.

Zusätzlich sollte noch:
1. Das Datum hat das Format YYYYMMDD bei der Eingabe (vorgegeben), das sollte aber in der Tabelle in dem Deutsche Format angezeigt werden : DDMMYYYY
2. Die Tabelle jomkas_users ist verknüpft mit einer weiteren Tabelle (jomkas_user_usergroup_map), welche die Spalten user_id und group_id enthält. Alle User werden hier unter User_id aufgelistet (entsprechend der IDs aus der "jomkas_User" Tabelle). Es sollen nur User mit dem Wert "9" in der Spalte group_id in die zu bildende Tabelle übernommen werden also berücksichtigt werden.
VG
Maggi

 

[BerndB]

Schick mal bitte das Ergebnis der Befehle
DESCRIBE jomkas_user_profiles;
und
DESCRIBE jomkas.users;
Daran kann man die genauen Feldtypen und längen entnehmen z.B wie
dein Datumsfeld wirklich gespeichert ist.

Gruss
Bernd