Abfrage über mehrere Tabellen mit leeren Ergebnissen

voumi · 29 April 2024

Hallo zusammen

Ich schaffe es wieder einmal nicht, eine etwas speziellere Abfrage zu machen und bedanke mich schon jetzt für jeden Tipp.

Mein Ziel ist eine Tabelle mit den Angaben zu den Instrumenten und deren Besitzer. Diese Tabelle sollte in etwa so aussehen: (Meine aktuelle Knacknuss sind die beiden hintersten Spalten.)

Instrument	Bezeichnung	114	117	119	120	121	123	124	126	132	134	135	136	161	162	234	235	Nachname Besitzer	Vorname Besitzer
Trompete	B-123456	123456	Privat	Besson	versilbert	Lieferant	Neu	1000	ohne Zubehör	muss in Stand gesetzt werden	War in Ausstellung	Vorbesitzer	Reparatur	500	01.01.2024	unten	1	Nachname 2	Vorname 2
Trompete	S-987456	987456	Privat	Sovereign								Vorbesitzer					2
Klarinette	B-787878																1	Nachname 3	Vorname 3
Cornet	Y-898989	898989	MGU	Yamaha													3	Nachname 4	Vorname 4

Ich habe diverse Tabellen, aus welchen die Abfrage zusammen geschustert werden soll. Ich versuche, diese Tabellen im nächsten Post anzuhängen.

Mit der ersten SQL-Abfrage erhalte ich eine Liste mit allen Angaben zu den Instrumenten, schaffe es aber nicht, die beiden "Spalten" mit den Angaben zu den Besitzern anzeigen zu lassen.

SQL:

SELECT usr_id,

id1.usd_value AS Instrument,
id2.usd_value AS Nummer,
id3.usd_value AS Instrumentennummer,
id4.usd_value AS Lagerort,
id5.usd_value AS Marke,
id6.usd_value AS Farbe,
id7.usd_value AS Lieferant,
id8.usd_value AS Neu_Occ,
id9.usd_value AS Preis,
id10.usd_value AS Zubehoer,
id11.usd_value AS Diverses,
id12.usd_value AS Bemerkungen,
id13.usd_value AS Vorbesitzer,
id14.usd_value AS Reparaturen,
id15.usd_value AS Zeitwert,
id16.usd_value AS Zeitwertdatum,
id17.usd_value AS Gravurort,
id18.usd_value AS Eigentum,
mem_begin,
mem_end

FROM adm_roles, adm_categories, adm_members, adm_users

LEFT JOIN adm_user_data id1 ON id1.usd_usr_id = usr_id
AND id1.usd_usf_id = 1

LEFT JOIN adm_user_data id2 ON id2.usd_usr_id = usr_id
AND id2.usd_usf_id = 2

LEFT JOIN adm_user_data id3 ON id3.usd_usr_id = usr_id
AND id3.usd_usf_id = 114

LEFT JOIN adm_user_data id4 ON id4.usd_usr_id = usr_id
AND id4.usd_usf_id = 117

LEFT JOIN adm_user_data id5 ON id5.usd_usr_id = usr_id
AND id5.usd_usf_id = 119

LEFT JOIN adm_user_data id6 ON id6.usd_usr_id = usr_id
AND id6.usd_usf_id = 120

LEFT JOIN adm_user_data id7 ON id7.usd_usr_id = usr_id
AND id7.usd_usf_id = 121

LEFT JOIN adm_user_data id8 ON id8.usd_usr_id = usr_id
AND id8.usd_usf_id = 123

LEFT JOIN adm_user_data id9 ON id9.usd_usr_id = usr_id
AND id9.usd_usf_id = 124

LEFT JOIN adm_user_data id10 ON id10.usd_usr_id = usr_id
AND id10.usd_usf_id = 126

LEFT JOIN adm_user_data id11 ON id11.usd_usr_id = usr_id
AND id11.usd_usf_id = 132

LEFT JOIN adm_user_data id12 ON id12.usd_usr_id = usr_id
AND id12.usd_usf_id = 134

LEFT JOIN adm_user_data id13 ON id13.usd_usr_id = usr_id
AND id13.usd_usf_id = 135

LEFT JOIN adm_user_data id14 ON id14.usd_usr_id = usr_id
AND id14.usd_usf_id = 136

LEFT JOIN adm_user_data id15 ON id15.usd_usr_id = usr_id
AND id15.usd_usf_id = 161

LEFT JOIN adm_user_data id16 ON id16.usd_usr_id = usr_id
AND id16.usd_usf_id = 162

LEFT JOIN adm_user_data id17 ON id17.usd_usr_id = usr_id
AND id17.usd_usf_id = 234

LEFT JOIN adm_user_data id18 ON id18.usd_usr_id = usr_id
AND id18.usd_usf_id = 235

WHERE rol_id = 87  AND mem_begin <= NOW() AND mem_end > NOW()

AND rol_valid = 1
AND rol_cat_id = cat_id
AND cat_org_id = 1
AND mem_rol_id = rol_id
AND mem_usr_id = usr_id
AND usr_valid = 1

ORDER BY Instrument, Nummer ASC;

Mit der zweiten Abfrage kann ich eine Liste mit den Instrumenten und den Besitzern anzeigen lassen. Schaffe es aber nicht, dass für nicht vorhandene Angaben eine NULL ausgegeben wird. Es werden nur jene "Datensätze" angezeigt, welche vollständig vorhanden sind.

SQL:

SELECT
CONCAT(id1.usd_value,' ', id2.usd_value) AS name,
id3.usd_value AS Instrument,
id4.usd_value AS Nummer,
id5.usd_value AS Eigentum,
v1.mem_begin,
v1.mem_end,
ure_urt_id,
ure_usr_id1,
ure_usr_id2

FROM

    adm_members v1

INNER JOIN (
    adm_user_data id1
INNER JOIN (
    adm_user_data id2
INNER JOIN (
    adm_user_data id3
INNER JOIN (
    adm_user_data id4
INNER JOIN (
    adm_user_data id5
    
    
INNER JOIN
    adm_user_relations

ON
id5.usd_usr_id = ure_usr_id2 AND id5.usd_usf_id = 235
)
ON
id4.usd_usr_id = ure_usr_id2 AND id4.usd_usf_id = 2
)
ON
id3.usd_usr_id = ure_usr_id2 AND id3.usd_usf_id = 1
)
ON
id2.usd_usr_id = ure_usr_id1 AND id2.usd_usf_id = 2
)
ON
id1.usd_usr_id = ure_usr_id1 AND id1.usd_usf_id = 1
)
ON
(v1.mem_rol_id = 2 OR v1.mem_rol_id = 50 OR v1.mem_rol_id = 78) AND v1.mem_usr_id = ure_usr_id1 AND ure_urt_id = 10  AND mem_begin < Now() AND mem_end > Now() 

GROUP BY ure_usr_id2
ORDER BY Name ASC;

Irgendwie habe ich das Gefühl, dass ich der Lösung schon sehr nahe bin, stehe aber trotzdem noch auf dem Schlauch.

Und noch eine Zusatzfrage:
Wenn in der usd_usf_id die 235 steht, ist die usd_value (aktuell) eine Zahl von 1 bis 3. Die entsprechenden Werte sehe ich in der Tabelle adm_user_fields unter usf_value_list. Ist es möglich, im Resultat anstelle der Zahl direkt den entsprechenden Wert anzeigen zu lassen? Bislang habe ich lediglich einen Weg gefunden, dies in der SQL-Abfrage zu "übersetzen". Das heisst aber, falls die Original-Werte geändert werden, diese in der SQL-Abfrage ebenfalls wieder angepasst werden müssen.

Besten Dank für Eure Unterstützung und liebe Grüsse aus der Schweiz.

voumi

voumi · 29 April 2024

Hier also die entsprechenden Daten:

adm_members

mem_id	mem_rol_id	mem_usr_id	mem_begin	mem_end
1	87	1	2010-05-01	9999-12-31
2	2	2	1970-02-01	9999-12-31
3	50	3	2020-02-25	9999-12-31
4	78	4	2021-03-02	9999-12-31
5	87	5	2010-01-02	9999-12-31
6	87	6	2015-05-31	9999-12-31
7	87	7	2016-06-08	9999-12-31

adm_user_relations

ure_id	ure_urt_id	ure_usr_id1	ure_usr_id2
1	10	1	2
2	11	2	1
3	10	6	3
4	11	3	6
5	10	4	7
6	11	7	4

adm_user_data

usd_id	usd_usr_id	usd_usf_id	usd_value
1	1	1	Trompete
2	1	2	B-123456
3	1	114	123456
4	1	117	Privat
5	1	119	Besson
6	1	120	versilbert
7	1	121	Lieferant
8	1	123	Neu
9	1	124	1000
10	1	126	ohne Zubehör
11	1	132	muss in Stand gesetzt werden
12	1	134	War in Ausstellung
13	1	135	Vorbesitzer
14	1	136	Reparatur
15	1	161	500
16	1	162	01.01.2024
17	1	234	unten
18	1	235	1
19	2	1	Nachname 2
20	2	2	Vorname 2
21	3	1	Nachname 3
22	3	2	Vorname 3
23	4	1	Nachname 4
24	4	2	Vorname 4
25	5	1	Trompete
26	5	2	S-987456
27	6	1	Klarinette
28	6	2	B-787878
29	7	1	Cornet
30	7	2	Y-898989
31	7	114	898989
32	7	117	MGU
33	7	119	Yamaha
34	5	114	987456
35	5	117	Privat
36	5	119	Sovereign
37	5	135	Vorbesitzer
38	5	235	2
39	6	235	1
40	7	235	3

adm_categories

cat_id	cat_org_id	cat_type	cat_name_intern
1	1	USF	BASIC_DATA
2	1	USF	SOCIAL_NETWORKS
3	1	USF	ADDITIONAL_DATA

adm_roles

rol_id	rol_cat_id	rol_name
2	4	Aktivmitglied
50	4	Kandidat
78	4	Musikschule
87	29	Instrument

adm_users

usr_id	usr_valid
1	1
2	1
3	1
4	1
5	1
6	1
7	1

BerndB · 29 April 2024

Leg doch mal die daten auf MySQL 8.0 , dann schreib ich dir die Abfrage mal etwas um

voumi · 29 April 2024

Vielen Dank für Deine schnelle Antwort. Ich bin zwar nicht ganz sicher, ob es das im Anhang ist, was Du brauchst. Und hoffentlich habe ich nichts vergessen. Ich habe nämlich einen Export der Originaldatenbank gemacht und die Daten etwas angepasst. Es scheint auf der von Dir verlinkten Plattform zu laufen. Allerdings erscheint noch eine Fehlermeldung wegen einer fehlenden Tabelle (adm_organisation). Da ich den ganzen Aufwand (vorerst) nicht noch einmal machen wollte, versuchte ich die Daten noch einzufügen. Leider habe ich es nicht geschafft, dies so zu machen, dass keine Fehlermeldung mehr erscheint. Vielleicht siehst Du, welcher Code den Fehler auslöst. Falls die Datei ansonsten OK und für Deine Unterstützung geeignet ist, mache ich den Export und die Anpassung nochmals.

BerndB · 29 April 2024

Danke erst mal. Wir haben uns hier leider etwas verpasst.

Ich habe jetzt erst morgen mittag wieder Zeit danach zu schauen. Damit ich das über die Plattform nutzen könnte hättest du mir noch die URL nach der letzen Änderung mitteilen müssen. Mit meinem link komme ich ja nur auf die STartseite.

Da du aber den Dump mitgeliefert hast kann ich das auch selbst importieren.

LG Bernd

voumi · 30 April 2024

Hier noch die beiden Dump-Dateien mit meinen Abfragen, welche nun zu den obigen Beispielen passen sollten. Warum die Besitzerliste als Resultat aktuell nicht drei Zeilen ausgibt, konnte ich leider bislang nicht nachvollziehen. Aber die Instrumentenliste ist eh näher an meinem Ziel. Dort fehlen "nur" die beiden Spalten mit den Vor- und Nachnamen (wo vorhanden). Das Resultat sollte in diesem Beispiel noch immer alle vier Instrumente beinhalten. Drei davon sollten eigentlich via adm_user_relations mit einer Person "verknüpft" sein. Cool wäre es zudem, wenn in der Spalte Eigentum nicht eine Zahl, sondern der entsprechende Wert des Radio_Buttons 'TEST' (Zeile 509) stehen würde.
Vielen Dank und liebe Grüsse
Fritz

Hier noch der Link: dbfiddle

ukulele · 30 April 2024

voumi schrieb:
Mit der zweiten Abfrage kann ich eine Liste mit den Instrumenten und den Besitzern anzeigen lassen. Schaffe es aber nicht, dass für nicht vorhandene Angaben eine NULL ausgegeben wird. Es werden nur jene "Datensätze" angezeigt, welche vollständig vorhanden sind.

Du müsstest vermutlich nur deine INNER JOINs durch LEFT JOINs ersetzen.

...und lass mal die komische Klammerung weg, du kannst alle Joins hintereinander weg schreiben.

voumi · 30 April 2024

Vielen Dank, ukulele, für den Tipp. Ich habe die Klammern weggelassen und die Joins ersetzt. Dann habe ich ein weiteres "Feld" hinzugefügt. Da dies aber ein Feld ist, bei welchem nicht alle Instrumente einen Eintrag haben, schrumpfte die Anzahl der Resultate. Das Ziel ist aber, dass die Anzahl gleich bleibt und dort NULL ausgegeben wird.

dabadepdu · 2 Mai 2024

Wenn Du ein weiteres Feld hinzufügst, schrumpft nichts.
Wenn Du eine weitere Tabelle per (inner) Join hinzufügst, kann sich die Anzahl der Ergebnisdatensätze verringern, die Menge "schrumpft".

Du musst dafür verstehen, wie Joins funktionieren. Das würde ich zu den SQL Grundlagen zählen.
Ein Standard Join, INNER JOIN, verknüpft alle zum Join Kriterium passenden Datensätze. Dies führt oft zu einer Verringerung der Ergebnissätze. (Weil nicht alle Datensatzwerte aus den gejointen Spalten der beiden Tabellen zueinander passen.)
Ein Outer Join, z.B. LEFT (OUTER) JOIN führt nie zu einer Verringerung der Datensätze. (Siehe Hinweis von @ukulele )

Dazu gibt es unzählige Tutorials. Hier ist ein beliebiger Such-Treffer nach dem Thema (deutsch)

SQL JOINs erklärt: 5 anschauliche SQL INNER JOIN-Beispiele für Einsteiger

Sehen Sie sich unsere SQL INNER JOIN Beispiele an und entdecken Sie die vielfältigen Anwendungsmöglichkeiten von JOIN für Anfänger. Machen Sie weiter und JOINen Sie Tabellen für bessere Dateneinblicke.

learnsql.de

Wenn Du Deine Abfrage änderst und dazu abstrakt schreibst, dass Du sie verändert hast und sie nicht das gewünschte Ergebnis zeigt, kann Dir niemand helfen, weil niemand weiß, was Du geändert hast.

ukulele · 2 Mai 2024

Vielleicht hast du noch einen INNER JOIN übersehen. Die Klammerung hat mich etwas verwirrt, aber ein LEFT JOIN allein kann keine Ergebnismenge reduzieren.

BerndB · 2 Mai 2024

Das ist die aktuelle Version. Auf der Produktion treten aber noch Probleme auf.
Eventuell stimmen die Tabellen nicht 100%

Code:

SELECT am.mem_id


, MAX( IF(ida.usd_usf_id=1, ida.usd_value, NULL)) AS Instrument
, MAX( IF(ida.usd_usf_id=2, ida.usd_value, NULL)) AS Nummer
, MAX( IF(ida.usd_usf_id=114, ida.usd_value, NULL)) AS Instrumentennummer
, MAX( IF(ida.usd_usf_id=117, ida.usd_value, NULL)) AS Lagerort
, MAX( IF(ida.usd_usf_id=119, ida.usd_value, NULL)) AS Marke
, MAX( IF(ida.usd_usf_id=120, ida.usd_value, NULL)) AS Farbe
, MAX( IF(ida.usd_usf_id=121, ida.usd_value, NULL)) AS Lieferant
, MAX( IF(ida.usd_usf_id=123, ida.usd_value, NULL)) AS Neu_Occ
, MAX( IF(ida.usd_usf_id=124, ida.usd_value, NULL)) AS Preis
, MAX( IF(ida.usd_usf_id=126, ida.usd_value, NULL)) AS Zubehoer
, MAX( IF(ida.usd_usf_id=132, ida.usd_value, NULL)) AS Diverses
, MAX( IF(ida.usd_usf_id=134, ida.usd_value, NULL)) AS Bemerkungen
, MAX( IF(ida.usd_usf_id=145, ida.usd_value, NULL)) AS Vorbesitzer
, MAX( IF(ida.usd_usf_id=136, ida.usd_value, NULL)) AS Reparaturen
, MAX( IF(ida.usd_usf_id=161, ida.usd_value, NULL)) AS Zeitwert
, MAX( IF(ida.usd_usf_id=162, ida.usd_value, NULL)) AS Zeitwertdatum
, MAX( IF(ida.usd_usf_id=234, ida.usd_value, NULL)) AS Gravurort
, MAX( IF(ida.usd_usf_id=235, ida.usd_value, NULL)) AS Eigentum

, MAX( IF(eig.usd_usf_id=1, eig.usd_value, NULL)) AS Nachname
, MAX( IF(eig.usd_usf_id=2, eig.usd_value, NULL)) AS Vorname


FROM adm_members AS am
LEFT JOIN adm_user_data AS ida ON ida.usd_usr_id = am.mem_id
LEFT JOIN adm_user_relations AS aur ON aur.ure_urt_id = 10 AND aur.ure_usr_id1 = am.mem_id
LEFT JOIN adm_user_data AS eig ON eig.usd_usr_id = aur.ure_usr_id2
WHERE am.mem_rol_id = 87
GROUP BY am.mem_id, ida.usd_usr_id , aur.ure_usr_id1
ORDER BY Instrument, Nummer ASC;

voumi · 2 Mai 2024

Dies ist finale Version, welche nun auch in der Produktion läuft. Mein Fehler war, dass ich in der Beispieldatenbank mit den diversen ID-Nummern stets bei 1 begonnen und somit die Übersichtlichkeit unnötig erschwert habe. (am.mem_id durch am.mem_usr_id ersetzt und schon funktioniert es auch in der Produktion)

SQL:

SELECT am.mem_usr_id

, MAX( usr.usr_login_name) AS Int_Nummer

, MAX( IF(ida.usd_usf_id=1, ida.usd_value, NULL)) AS Instrument
, MAX( IF(ida.usd_usf_id=2, ida.usd_value, NULL)) AS Nummer
, MAX( IF(ida.usd_usf_id=114, ida.usd_value, NULL)) AS Instrumentennummer
, MAX( IF(ida.usd_usf_id=117, ida.usd_value, NULL)) AS Lagerort
, MAX( IF(ida.usd_usf_id=119, ida.usd_value, NULL)) AS Marke
, MAX( IF(ida.usd_usf_id=120, ida.usd_value, NULL)) AS Farbe
, MAX( IF(ida.usd_usf_id=121, ida.usd_value, NULL)) AS Lieferant
, MAX( IF(ida.usd_usf_id=123, ida.usd_value, NULL)) AS Neu_Occ
, MAX( IF(ida.usd_usf_id=124, ida.usd_value, NULL)) AS Preis
, MAX( IF(ida.usd_usf_id=126, ida.usd_value, NULL)) AS Zubehoer
, MAX( IF(ida.usd_usf_id=132, ida.usd_value, NULL)) AS Diverses
, MAX( IF(ida.usd_usf_id=134, ida.usd_value, NULL)) AS Bemerkungen
, MAX( IF(ida.usd_usf_id=135, ida.usd_value, NULL)) AS Vorbesitzer
, MAX( IF(ida.usd_usf_id=136, ida.usd_value, NULL)) AS Reparaturen
, MAX( IF(ida.usd_usf_id=161, ida.usd_value, NULL)) AS Zeitwert
, MAX( IF(ida.usd_usf_id=162, ida.usd_value, NULL)) AS Zeitwertdatum
, MAX( IF(ida.usd_usf_id=234, ida.usd_value, NULL)) AS Gravurort
, MAX( IF(ida.usd_usf_id=235, ida.usd_value, NULL)) AS Eigentum
, SUBSTRING_INDEX(SUBSTRING_INDEX(ausw.usf_value_list, '\r\n', MAX( IF(ida.usd_usf_id=235, ida.usd_value, NULL)) ), '\n', -1 ) AS Eigentum2

, MAX( IF(eig.usd_usf_id=1, eig.usd_value, NULL)) AS Nachname
, MAX( IF(eig.usd_usf_id=2, eig.usd_value, NULL)) AS Vorname

FROM adm_members AS am
LEFT JOIN adm_user_data AS ida ON ida.usd_usr_id = am.mem_usr_id
LEFT JOIN adm_user_relations AS aur ON aur.ure_urt_id = 10 AND aur.ure_usr_id2 = am.mem_usr_id
LEFT JOIN adm_user_data AS eig ON eig.usd_usr_id = aur.ure_usr_id1
LEFT JOIN adm_users AS usr ON usr.usr_id = am.mem_usr_id
CROSS JOIN adm_user_fields ausw ON ausw.usf_id = 235
WHERE am.mem_rol_id = 87 AND am.mem_begin < NOW() AND am.mem_end > NOW()
GROUP BY am.mem_usr_id, ida.usd_usr_id , aur.ure_usr_id1
ORDER BY Instrument, Nummer ASC;

Zudem habe ich nun noch eine weitere Tabelle verknüpft, wo ich den usr_login_name abhole. Nun ist alles so, wie ich wollte.
Besten Dank für die Hilfe.
Ich habe mir auch die Tutorials über die JOINS angesehen und denke, dass ich sie theoretisch einigermassen verstanden habe. In der Praxis stolpere ich immer wieder über meine Datenstruktur, welche irgendwie stets noch einen Schritt mehr zu erfordern scheint.

dabadepdu · 3 Mai 2024

voumi schrieb:
In der Praxis stolpere ich immer wieder über meine Datenstruktur, welche irgendwie stets noch einen Schritt mehr zu erfordern scheint.

Deine Werte liegen einem Key Value Store. Eine Tabellenadaption von Key-Value Datenbanken.
Es ist nicht elegant, das mit SQL abzufragen.
Aber auch dafür gibt es Prinzipien und Vorgehensweisen. Google hilft da sicher auch weiter.

Abfrage über mehrere Tabellen mit leeren Ergebnissen

voumi

Benutzer

voumi

Benutzer

BerndB

Datenbank-Guru

voumi

Benutzer

Anhänge

BerndB

Datenbank-Guru

voumi

Benutzer

Anhänge

ukulele

Datenbank-Guru

voumi

Benutzer

dabadepdu

Datenbank-Guru

SQL JOINs erklärt: 5 anschauliche SQL INNER JOIN-Beispiele für Einsteiger

ukulele

Datenbank-Guru

BerndB

Datenbank-Guru

voumi

Benutzer

dabadepdu

Datenbank-Guru

Ähnliche Themen