Live-Forum - Die aktuellen Beiträge
Anzeige
Archiv - Navigation
1700to1704
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Inhaltsverzeichnis

SQL-Abfrage Union, Join

SQL-Abfrage Union, Join
03.07.2019 16:56:11
Toni
Hallo Excel-Süchtige und Sympathisanten,
ich versuche mich gerade an ADO-Grundfragestellungen und in diesem Zusammenhang habe ich mir diese Aufgabe gestellt:
Schritt1: Kopiere 2 Tabellen (Datei1 und Datei3) in einer Zieltabelle via Union untereinander, wobei eine der Tabellen weniger Spalten und Spalten-Alias´ hat.
Schritt2: hole aus Datei2 analog zum SVerweis Daten an die Zieltabelle --> Join.
Noch simuliere ich das in "einer einzigen" Datei mit mehreren Tabellenblättern, später werden es separate Dateien: Zieldatei.xlsx / Datei1.xlsx / Datei2.xlsx / Datei3.xlsx
Eine Testdatei mit Erklärungen findet Ihr hier:
https://www.herber.de/bbs/user/130715.xlsm
bzw.:
https://www.herber.de/bbs/user/130716.xlsx
Das sind meine bisherigen Errungenschaften:
  • 
    Sub Abfrage()
    Dim cn As Object
    Dim rs As Object
    Dim strFile As String
    Dim strCon As String
    Dim strSQL As String
    Dim s As String
    Dim i As Integer, j As Integer
    strFile = ActiveWorkbook.FullName
    strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strFile _
    & ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";"
    Set cn = CreateObject("ADODB.Connection")
    Set rs = CreateObject("ADODB.Recordset")
    cn.Open strCon
    'Union:
    strSQL = "SELECT aaaa, bbbb, cccc, dddd, eeee, ffff, gggg, hhhh FROM ( " _
    & "SELECT Spalte1 as aaaa, Spalte2 as cccc, Spalte3 as gggg, Spalte4 as hhhh " _
    & "FROM [Datei3Tabelle1$] " _
    & "UNION ALL " _
    & "SELECT aaaa, bbbb, cccc, dddd, eeee, ffff, gggg, hhhh  " _
    & "FROM [Datei1Tabelle1$] ) "
    'Join:
    strSQL = strSQL & "SELECT * " _
    & "FROM [Datei1Tabelle1$] " _
    & "Left Join [Datei2Tabelle1$]" _
    & "Where Datei1Tabelle1$.aaaa = Datei2Tabelle1$.Suchwert1 "
    rs.Open strSQL, cn, 3, 3
    Worksheets("Zieltabelle").Cells(1, 1).CopyFromRecordset rss
    End Sub
    


  • Ich Würde mich wie immer sehr freuen über Lob, Anerkennung und Bestätigung :), aber natürlich auch über Vorschläge Eurerseits!
    lG und Danke im Voraus
    Toni

    17
    Beiträge zum Forumthread
    Beiträge zu diesem Forumthread

    Betreff
    Datum
    Anwender
    Anzeige
    AW: SQL-Abfrage Union, Join
    03.07.2019 20:03:47
    Rob
    Hi Toni,
    sieht sehr struktuiert und leserlich aus Dein Code! In Visual Basic sehr wichtig.
    Du könntest Die Initiierung Deiner Variablen verkürzen. Z.B.:
    
    Dim strFile as String, strCon as String, strSQL As String
    
    Außerdem ist die Hungarian Notation lt. Microsoft ein DO NOT: https://docs.microsoft.com/de-de/dotnet/standard/design-guidelines/general-naming-conventions
    Schadet auch der Leserlichkeit - VB ist ohnehin schon sehr 'wortlastig'. Von Abkürzungen wie cn oder rs wird auch abgeraten, obwohl ich das auch schon mal nutze.
    Ich würde an Deiner Stelle auch eher mit Early Binding programmieren - so hast Du die Intellisense zur Verfügung. Kannst immer noch auf Late Binding umstellen, wenn Du Dein Makro veröffentlichst.
    Mir ist noch aufgefallen, dass Du nur Dein Recordset in das Arbeitsblatt übergibst (.CopyFromRecordset). Benötigst Du keinen Tabellen-Header?
    
    Dim Fields As ADODB.Fields
    Dim i As Integer
    recordSet.Open SQL, ConnStr, adOpenForwardOnly, adLockReadOnly, adCmdText
    Set Fields = recordSet.Fields
    For i= 0 To (objFields.Count - 1)
    Debug.Print Fields.Item(i).Name
    Next
    
    Grüße,
    Rob
    Anzeige
    AW: SQL-Abfrage Union, Join
    03.07.2019 21:27:56
    Toni
    Hallo Rob,
    da waren eine ganze Menge neue Tipps für mich dabei, für die und Deine Mühe ich mich bei Dir bedanken möchte!! Der Zusammenhang zwischen Intellisense und Early Binding war mir so nicht bewusst. Werde ich beherzigen. Überschriften hatte ich auch nicht auf dem Schirm, das probiere ich aus, aber jetzt zu den Details:
    hier:
    Dim Fields As ADODB.Fields  
    

    bringt er eine Fehlermeldung: "Fehler beim Kompilieren. Benutzerdefinierter Typ nicht definiert" ? Da muss sicher ein Verweis noch gesetzt werden, weißt Du evtl. welcher?
    In dieser SQL-Anweisung mosert er, weil die Spaltenanzahl nicht übereinstimmt. Ich würde vermuten, dass man mit Deinem Hinweis auf fields auch ebenso Überschriften in die Tabelle mit den wenigen Spalten hinein bekommt, allein wie :) ?
    strSQL = "SELECT aaaa, bbbb, cccc, dddd, eeee, ffff, gggg, hhhh FROM ( " _
    & "SELECT Spalte1 as aaaa, Spalte2 as cccc, Spalte3 as gggg, Spalte4 as hhhh " _
    & "FROM [Datei3Tabelle1$] " _
    & "UNION ALL " _
    & "SELECT aaaa, bbbb, cccc, dddd, eeee, ffff, gggg, hhhh  " _
    & "FROM [Datei1Tabelle1$] ) "
    

    Bei der Passage hier wars mir schon klar, dass das nicht funktionieren kann, allein schon weil nur die Datei1 dort angegeben ist.
    strSQL = strSQL & "SELECT * " _
    & "FROM [Datei1Tabelle1$] " _
    & "Left Join [Datei2Tabelle1$]" _
    & "Where Datei1Tabelle1$.aaaa = Datei2Tabelle1$.Suchwert1 "
    

    Da er mir weder mit
    rs.Open strSQL, cn, 3, 3
    

    noch mit (angepasst natürlich auf meine Variablen):
    Recordset.Open Sql, ConnStr, adOpenForwardOnly, adLockReadOnly, adCmdText 
    

    etwas auswirft, habe ich grad gar keinen Plan.
    Er steigt hier aus
    rs.Open strSQL, cn, 3, 3
    

    mit dem Hinweis "Syntaxfehler in From-Klausel", jetzt bin ich leider in dieser Syntax noch so gar nicht zu Hause
    Bin gerade am Überlegen, ob ich mir da einen Volkshochschulkurs buche erst einmal :))
    Vielleicht hast Du oder jmd. anderes ja dazu noch Tipps. Wäre sehr willkommen!
    Danke und lG
    Toni
    Anzeige
    AW: SQL-Abfrage Union, Join
    04.07.2019 08:47:41
    Rob
    Guten Morgen,
    der Verweis für ADODB heißt 'Microsoft ActiveX Data Objects 6.1 Library'.
    Deine SQL-Anweisung ist ehrlich gesagt für mich nicht nachvollziehbar, sieht aber schon sehr abenteuerlich aus. Am besten Du liest Dich dazu mal ein - Quellen gibt es hierzu genug.
    Grüße, Rob
    AW: SQL-Abfrage Union, Join
    04.07.2019 19:36:59
    Toni
    Hallo,
    habe leider nichts Anständiges gefunden. Sind alles irgendwelche SQL-Statements, die im VBA so nicht funktionieren. Mein aktueller Stand, wobei ich das erzeugen von neuen Spalten(-Überschriften), die in Datei3Tabelle1 noch nicht vorhanden sind erst einmal außen vorgelassen habe:
    strSQL = SELECT Datei2Tabelle1.Zielwert1, Zwischen.aaaa, Zwischen.cccc, Zwischen.gggg, Zwischen. _
    hhhh
    FROM
    (SELECT Datei1Tabelle1.aaaa,Datei1Tabelle1.cccc,Datei1Tabelle1.gggg,Datei1Tabelle1. _
    hhhh
    FROM Datei1Tabelle1
    Union
    SELECT Datei3Tabelle1.Spalte1 as aaaa, Datei3Tabelle1.Spalte2 as cccc,  _
    Datei3Tabelle1.Spalte3 as gggg, Datei3Tabelle1.Spalte4 as hhhh
    FROM Datei3Tabelle1
    ) Zwischen
    LEFT JOIN Datei2Tabelle1
    ON Zwischen.aaaa = Datei2Tabelle1.Suchwert1
    
    Damit ist der VBA-Code komplet rot ^^
    Vielleicht einer eine Idee?
    Danke und lG
    Toni
    Anzeige
    Haken gesetzt owT
    04.07.2019 19:37:43
    Toni
    AW: SQL-Abfrage Union, Join
    04.07.2019 20:17:17
    ChrisL
    Hi Toni
    strSQL = "SELECT ...."
    cu
    Chris
    AW: SQL-Abfrage Union, Join
    04.07.2019 20:36:48
    Toni
    Hi Chris,
    das hat's schonmal etwas schöner gefärbt, Danke!
    strSQL = "SELECT Datei2Tabelle1.Zielwert1, Zwischen.aaaa, Zwischen.cccc, Zwischen.gggg, Zwischen.hhhh" _
    & "FROM" _
    & "(SELECT Datei1Tabelle1.aaaa,Datei1Tabelle1.cccc,Datei1Tabelle1.gggg,Datei1Tabelle1.hhhh" _
    & "FROM Datei1Tabelle1" _
    & "Union " _
    & "SELECT Datei3Tabelle1.Spalte1 as aaaa, Datei3Tabelle1.Spalte2 as cccc," _
    & "Datei3Tabelle1.Spalte3 as gggg, Datei3Tabelle1.Spalte4 as hhhh" _
    & "FROM Datei3Tabelle1" _
    & ") Zwischen" _
    & "LEFT JOIN Datei2Tabelle1" _
    & "ON Zwischen.aaaa = Datei2Tabelle1.Suchwert1"
    Die Fehlermeldung jetzt:
  • "Fehler in From-Anweisung"

  • ?
    lG
    Toni
    Anzeige
    AW: SQL-Abfrage Union, Join
    05.07.2019 07:18:22
    ChrisL
    Hi Toni
    Erstelle einen funktionierenden SQL-String z.B. in Access und kopiere dann nach VBA. Einfach so findet man den Fehler meistens nicht.
    cu
    Chris
    AW: SQL-Abfrage Union, Join
    05.07.2019 13:02:51
    Toni
    Hi Chris,
    habe leider kein Access und ich weiß ja eben nicht genau , wie man einen solchen SQL-String richtig erstellt. Die meisten SQL-Lösungen im Netz sind einfacherer Natur (Select From Where ...).
    Hätte ja nicht gedacht, dass das so schwer ist, 2 Tabellen untereinander und dann eine daneben zu tun, aber man lernt nie aus ...
    Da habe ich wohl einen heiligen Gral entdeckt ...
    lG
    Toni
    Keiner mit SQL, ADO-Kenntnissen?
    06.07.2019 21:50:35
    Toni
    Hallo,
    Meine Ergebnisse bis hierhin, Einiges musste angepasst werden (v. a. Tab-Namen, Überschriften):
    https://www.herber.de/bbs/user/130762.xlsm
    Union und die "Spalte-As-Setzungen" sind soweit umgesetzt.
    Wie man jedoch von der einen Tabelle sämtliche Überschriften anzeigt, wo die andere Tabelle keine Daten hat, ist mir noch ein Rätsel. Ebenso wie man jetzt über LEFT JOIN die 3. Tabelle an die Zieltabelle bekommt. Wobei letzterer Punkt Priorität hat.
    Eigentlich rechne ich nicht mehr mit Lösungen, ist wohl so ne Art Herrschaftswissen, aber Wunder gibts ja immer wieder :)
    lG
    Toni
    Anzeige
    Als XLSX ...
    06.07.2019 21:55:47
    Toni
    https://www.herber.de/bbs/user/130763.xlsx
    Sub TabellenZusammenfassen()
    Dim cn As Object
    Dim rs As Object
    Dim strConnection As String
    Dim strSQL As String
    tbl_Ziel.UsedRange.ClearContents
    tbl_von1.Rows(1).Copy Destination:=tbl_Ziel.Range("A1")
    Set cn = CreateObject("ADODB.CONNECTION")
    strConnection = _
    "DRIVER={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)}; DBQ=" _
    & ThisWorkbook.FullName
    With cn
    .Open strConnection
    strSQL = "SELECT Ort, Postleitzahl, Strasse, Nummer FROM [tbl_von1$] UNION " & _
    "SELECT POrt As Ort, PPostleitzahl As Postleitzahl, PStrasse AS Strasse, Ersatz As  _
    Nummer FROM [tbl_von2$]"
    Set rs = CreateObject("ADODB.RECORDSET")
    With rs
    .Source = strSQL
    .ActiveConnection = strConnection
    .CursorType = adOpenKeyset
    .LockType = adLockOptimistic
    .Open
    tbl_Ziel.Range("A2").CopyFromRecordset rs
    tbl_Ziel.Columns("A:E").AutoFit
    .Close
    End With
    End With
    cn.Close
    Set cn = Nothing
    Set rs = Nothing
    End Sub
    

    Anzeige
    Als XLSX ...
    06.07.2019 21:56:06
    Toni
    https://www.herber.de/bbs/user/130763.xlsx
    Sub TabellenZusammenfassen()
    Dim cn As Object
    Dim rs As Object
    Dim strConnection As String
    Dim strSQL As String
    tbl_Ziel.UsedRange.ClearContents
    tbl_von1.Rows(1).Copy Destination:=tbl_Ziel.Range("A1")
    Set cn = CreateObject("ADODB.CONNECTION")
    strConnection = _
    "DRIVER={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)}; DBQ=" _
    & ThisWorkbook.FullName
    With cn
    .Open strConnection
    strSQL = "SELECT Ort, Postleitzahl, Strasse, Nummer FROM [tbl_von1$] UNION " & _
    "SELECT POrt As Ort, PPostleitzahl As Postleitzahl, PStrasse AS Strasse, Ersatz As  _
    Nummer FROM [tbl_von2$]"
    Set rs = CreateObject("ADODB.RECORDSET")
    With rs
    .Source = strSQL
    .ActiveConnection = strConnection
    .CursorType = adOpenKeyset
    .LockType = adLockOptimistic
    .Open
    tbl_Ziel.Range("A2").CopyFromRecordset rs
    tbl_Ziel.Columns("A:E").AutoFit
    .Close
    End With
    End With
    cn.Close
    Set cn = Nothing
    Set rs = Nothing
    End Sub
    

    Anzeige
    AW: Als XLSX ...
    08.07.2019 10:19:22
    EtoPHG
    Hallo Toni (aka JK),
    Warum SQL in einer Mappe einsetzen, wenn es nur um Daten innerhalb dieser geht, erschliesst sich mir nicht. Die SQL-Syntax des ADODB-Excel-Treibers ist auf einem minimalen SQL Standard, der weit weg von heutigen SQL-DB's ist.
    Ich behaupte, dass du mit PowerPivot oder PowerQuery viel schneller ans Ziel kommst.
    Übrigens: Was verstehst unter Herrschaftswissen? Google liefert ein paar Millionen Antworten auf die Suche für "sql union and join in same query" und einen Heiligen Gral hast du mitnichten entdeckt, höchstens ein moderndes Grab!
    Gruess Hansueli
    Anzeige
    AW: Als XLSX ...
    08.07.2019 11:32:11
    Toni
    Hallo Hansueli,
    hast Du wahrscheinlich übersehen (s. Einstiegsbeitrag):
    "Noch simuliere ich das in "einer einzigen" Datei mit mehreren Tabellenblättern, später werden es separate Dateien: Zieldatei.xlsx / Datei1.xlsx / Datei2.xlsx / Datei3.xlsx"
    Dann: PQ oder PP stehen mir in der anvisierten Excel-Version nicht zur Verfügung.
    Wie würdest Du die Aufgabe angehen, wenn nicht mit ADO-SQL? VBA hätte ich bereits, scheint mir umständlicher (Dateien öffnen, auslesen, zusammenschustern, ..., schließen) als 'ein einziges' SQL-Statement?.
    Mit den Google-Einträgen komme ich wenig zurecht, weil in der Mehrzahl auf Access und andere SQL-Lösungen gemünzt.
    lG
    Toni
    Anzeige
    AW: Als XLSX ...
    08.07.2019 12:18:06
    EtoPHG
    Hallo Toni,
    Und was willst du mir damit mitteilen?
    Mein Aussage war IMHO klar: Nicht mit SQL von Excel auf Excel zugreifen!
    Was ist denn deine anvisierte XL Version?
    Die genaue Aufgabenbeschreibung kenne ich nicht und kann sie auch nicht aus deiner Beispielmappe ableiten. Ich gehe grundsätzlich so an XL-Aufgaben:
    1. Kann ich die Resultate mit einfache Standard-Formeln erreichen?
    2. Helfen mit Standard-Funktionalitäten wie Konsolidierung, Pivottabellen, Datenimporte?
    3. Wären ev. Powerpivot und/oder PowerQuery effizienter?
    Erst wenn ich hier wirklich an Grenzen stosse weiche ich ggf. auf VBA aus.
    In VBA brauche ich oft ADODB aber v.a. im Zusammenhang mit oracle, sql-server datenbanken und auch da nur dann, wenn ich mit den heutigen Datenimport-Möglichkeiten von XL nicht weiterkomme.
    SQL auf XL-Dateien habe ich experimentell vor Jahre gebraucht, hab dann aber dieser Möglichkeit den Rücken gekehrt.
    Gruess Hansueli
    AW: Als XLSX ...
    08.07.2019 13:24:49
    Toni
    Hallo Hansueli,
    Danke für Deine Information. Grundsätzlich ist das auch sehr hilfreich!
    und genau dieses experimentell ausprobieren: da bin ich gerade. Ich frage mich, warum viele Schritte, wenn vllt. ein einziges SQL-Statement reicht ...
    im Prinzip so:
    Left Join (Datei1 Union Datei2) Datei3
    Datei1.Schlüssel = Datei2.Schlüssel = Datei3.Schlüssel
    mit der Besonderheit, dass Datei1 mehr Spalten hat als Datei2. Diese Spalten bleiben dann nach der Zusammenführung für Datei2 einfach frei.
    lG
    Toni
    XL 2010 owT
    08.07.2019 13:42:40
    Toni

    80 Forumthreads zu ähnlichen Themen

    Anzeige
    Anzeige

    Beliebteste Forumthreads (12 Monate)

    Anzeige

    Beliebteste Forumthreads (12 Monate)

    Anzeige
    Anzeige
    Anzeige