SQL MSSQL Unterabfrage

[LauraM]

Hallo, vielleicht könnte jemand noch am Fr die Abgare sich angucken, ich bin verzweifelt.
Ich möchte eine Abfrage mit Unterabfrage bauen, um auf richtigen Ergebnis zu kommen.
Meine Haupttabelle prod besteht aus prodnr und telnr
Zweite Tabelle besteht auch aus prodnr und adresse
Allerdings ist das keine Beziehung 1:1, da es mehr Adressen gibt als Prodnr.
Deshalb die tab2 sieht so aus :

prodnr   adresse
12           abc
12           mno
14            tala

Ich versuche eine Abfrage zu bauen, wo ich im Antwort bekomme:

prodnr    telnr    adressse     adresse
12           123     abc                mno
14            456    tala                null

Wie kann ich das tun?

Das funktioniert nicht:

select prodnr, telnr,
    (select  prodnr, adresse from tab2 where tab1.prodnr=tab2.prodnr)
    from tab1

Danke für jede Tipp!

 

[abcddcba]

Das funktioniert nicht:

ist keine nuetzliche Fehlerbeschreibung ...

 

[Defender1st]

Hilft dir evtl FOR XML PATH weiter?

select tab1.prodnr, tab1.telnr,
substring((select ', '+tab2.adresse from tab2 where tab2.prodnr = tab1.prodnr for xml path('')),2,1000) Adressen
from tab1

Solltest du nen SQL Server ab 2017 einsetzen kannst du auch string_agg nutzen

 

[RalphS]

Die tab2 aus dem Beispiel gefällt mir gar nicht. Nicht normalisiert, ergo das wird so nicht funktionieren, wie Du Dir das vorstellst.

Was ist denn in tab2 der Primärschlüssel? Ich unterstelle auch mal, daß es ein etwas konstruiertes Beispiel ist - eine Abbildung Produkt auf Adresse ergibt für mich jedenfalls keinen Sinn.

Ergo wäre meine Empfehlung, das Schema zu überarbeiten... in der Hoffnung, daß das so nicht fest vorgegeben ist und daß da nicht jemand(tm) DBMS mit Excel verwechselt hat. Wäre leider nicht das erste Mal.

 

[Raijin]

Es ist zudem nicht Aufgabe der Datenbank, den Inhalt schön darzustellen. Hat man mehrere Tabellen mit einer 1:1,1:n oder auch m:n Beziehung, ruft man die gewünschten Daten mit einem JOIN ab und bereitet sie in der GUI entsprechend auf. Eine Datenbank speichert Daten und ermöglicht den Zugriff auf sowie die Filterung der Daten. Die Verarbeitung oder Darstellung dieser Daten obliegt nicht der Datenbank.

 

[pizza4ever]

Das wird mit einer Temp tabelle gehen, aber leider habe ich keinen MSSQL Server zu hand um dir eine geanau Syntax zu liefern.

http://www.sqlservertutorial.net/sql-server-basics/sql-server-temporary-tables/

Du wirst nicht herumkommmen ein bisschen nach dem Index und den Selects zu schauen und v.a. nach dem zweiten Update, aber die Idee dahinter ist:

1.) erstelle ein Temp tabelle
2) Schreibe die Tel in die Temp Tabelle
3.) Update Adresse1 mit einem Wert aus der Ursprungstabelle
4.) Update Adresse 2 mit den "nicht verbrauchten" Werten
5.) select aus der Temp tabelle

ggf. kann man 2.) und 3.) zusammenfassen

 

[parats]

Für das hässliche Konstrukt oben sollte dir ein recursive CTE helfen. Damit lassen sich solche Konstrukte noch abfragen, wenn auch umständlich.

Abhängig von der MSSQL Version gibt es diese aber nicht zwangsläufig. Ich arbeite überwiegend mit der APS Engine, die ist ziemlich eingedampft und hat bspw. keine recursive CTEs.

 

[RalphS]

Mit den "nicht verbrauchten Werten"? Wie stellt ihr euch das vor?

CTE wäre eine Idee, wobei ich mich trotzdem frage, woran man das im Beispiel festmachen wollen würde.

Das Problem ist, daß tab2 oben ein dreidimensionales Konstrukt in einer zweidimensionalen Matrix sein will. Das geht notwendigerweise schief. Stattdessen bräuchte man drei Tabellen
- products
id PK
name
- addresses
id PK
straße, nr, plz, ort etc etc etc
- prod_x_address
PK| p_id FK => products(id)
PK| a_id FK => adresses(id)

und dann definiert man auf prod_x_adresses die Bezüge, die derzeit in tab2 abgebildet sind, mit einer funktionalen Abbildung p_id => a_id, die man so einfach eintragen kann.

Für Queries mit einem zweifachen JOIN und schon hat man, was man braucht.

 

[parats]

Was man typischerweise mit einer rekursiven CTE haben will, ist das misslungene Konstrukt in eine Spalte zu mergen um dann dort in der Applikation den Rest zu handeln. Es ist ja auch nicht der primäre Anwendungsfall, denn eigentlich rollt man damit prima Hierarchien auf. Aber Missbrauch geht immer in der Technik. Idealerweise hat man natürlich ein korrektes Datenmodell wie bspw. deins da oben. Je nach Anforderung kann ich mir aber auch SCD2 Konstrukte vorstellen, sofern es historische Daten sind.

 

[pizza4ever]

Mit den "nicht verbrauchten Werten"? Wie stellt ihr euch das vor?

Join auf die Temp Tabelle und dann where x <> y bzw not null (je nachdem wie man das Update baut)...?

 

[Raijin]

Das Hauptproblem ist, dass man ausgehend von den mittlerweile 7* Threads von @LauraM sagen kann, dass das Design der Datenbank schlicht und ergreifend nicht den Anforderungen gerecht wird. Eine relationale Datenbank dient der nicht-redundanten Speicherung von Daten. Die Aufbereitung selbiger ist Aufgabe der bedienenden Anwendung. Je mehr Skripte man innerhalb des SQL-Servers schreiben muss und je komplexer diese sind, umso deutlicher wird, dass die Datenbasis mangelhaft ist.

Die Auswertung der Daten, die hübsche Darstellung, etc. erfolgt dann in der jeweiligen Anwendung. Es ist nicht sinnvoll, immer alle SELECT-Anweisungen derart aufzublasen, nur damit man eine "schöne" Tabelle als Ergebnis bekommt. Solche Konstrukte setzt man bei Bedarf ein, aber wenn sie zur zwingenden Voraussetzung werden, ist der Ansatz schon verkehrt.

* nu sind's 8

 

[LauraM]

Hilft dir evtl FOR XML PATH weiter?

select tab1.prodnr, tab1.telnr,
substring((select ', '+tab2.adresse from tab2 where tab2.prodnr = tab1.prodnr for xml path('')),2,1000) Adressen
from tab1

Solltest du nen SQL Server ab 2017 einsetzen kannst du auch string_agg nutzen

Hi, danke für den Tipp mit string_agg. Ich habe jetzt alle Beispiele ausprobiert und bekomme immer die gleiche Meldung:
Meldung 195, Ebene 15, Status 10, Zeile 109
'STRING_AGG' wird nicht als Name einer integrierten Funktion erkannt.
Hat das was mit MS SQL Server zu tun?

Es funktioniert nicht, da ich MSSQL 2014 habe.

Gut, muss ich was anderes für Verketten suchen.

 

[Andreas_]

Hier ist eine Lösung, bei der eine eigene Funktion dafür geschrieben wurde:
https://stackoverflow.com/questions/51331640/how-to-write-the-t-sql-string-agg-function
https://github.com/orlando-colamatteo/ms-sql-server-group-concat-sqlclr

An Stelle von STRING_AGG kannst Du dann dbo.GROUP_CONCAT benutzen.

 

[LauraM]

Guten Morgen,
danke Andreas_, ich versuche, vielleicht klappt das

 

[LauraM]

Hier ist eine Lösung, bei der eine eigene Funktion dafür geschrieben wurde:
https://stackoverflow.com/questions/51331640/how-to-write-the-t-sql-string-agg-function
https://github.com/orlando-colamatteo/ms-sql-server-group-concat-sqlclr

An Stelle von STRING_AGG kannst Du dann dbo.GROUP_CONCAT benutzen.

Könntest du mir bitte doch mal helfen?

ich habe ähnliche Aufgabe, wie hier:
https://vike.io/de/230081/ , das ist eine Tabelle mit 4 Spalten. Allerdings sind 3 Spalten, die ich zuordnen möchte:
ist:

Nr	Tel	Adresse	Kunde
123	0178	NULL	Müller
123	0146	NULL	Schmidt
125	0145	Achornstr	Mayer

soll:

Nr	Tel	Adresse	Kunde	Tel	Adresse	Kunde
123	0179	Null	Müller	0146	Null	Schmidt
125	0146	Achornstr.	Mayer

Wie kann ich mit Verketten am besten hier umgehen?

 

[LauraM]

Ich habe so eine Abfrage modifiziert:

SELECT nr,
    STUFF((SELECT DISTINCT ' '+ a.tel
        FROM tab1 a
        WHERE a.nr= b.nr FOR XML PATH('')),1 ,1, '') tel,
    STUFF((SELECT DISTINCT ';'+ a.adresse
        FROM tab1 a
        WHERE a.nr= b.nr FOR XML PATH('')),1 ,1, '') adresse,
    STUFF((SELECT DISTINCT '; '+ a.kunde
        FROM tab1 a
        WHERE a.nr= b.nr FOR XML PATH('')),1 ,1, '') kunde
        FROM tab1 b
    group by nr

Allerdings als Ergebnis bekomme ich:

nr	Tel	adresse	kunde
0123	0178. 0146	null, null	Müller, schmidt

 

[abcddcba]

Verstehst du auch warum? Kennst du left-joins eigentlich?

 

[LauraM]

Guten Morgen, ich kenne joins (left, right ).
Wie kann ich hier join integrieren?

 

[Oelepoeto]

Wenn du eine unbekannte Anzahl an Zeilen in eine unbekannte Anzahl an Spalten umwandeln möchtest (es können ja bestimmt auch 3 oder 100 Nummern und Namen zu deine Nr vorhanden sein) bist du zwangsläufig auf dynamischen SQL Code angewiesen. Ich denke nicht, dass das für dich ein geeigneter Lösungsweg ist..

Ergänzung (10. Dezember 2019)

Ich habe so eine Abfrage modifiziert:

SELECT nr,
STUFF((SELECT DISTINCT ' '+ a.tel
FROM tab1 a
WHERE a.nr= b.nr FOR XML PATH('')),1 ,1, '') tel,
STUFF((SELECT DISTINCT ';'+ a.adresse
FROM tab1 a
WHERE a.nr= b.nr FOR XML PATH('')),1 ,1, '') adresse,
STUFF((SELECT DISTINCT '; '+ a.kunde
FROM tab1 a
WHERE a.nr= b.nr FOR XML PATH('')),1 ,1, '') kunde
FROM tab1 b
group by nr

Da du in einen anderen Beitrag Mal von ca. 1 Million Datensätze geredet hast: bei der Menge werden solche Abfragen (Unterabfragen im Select) zu Laufzeitprobleme führen