none
Left Join als SubQuery

    Frage

  • Hallo Gemeinde,

    es will mir nicht gelingen ein SubQuery mit einem Left Join in Access zu erstellen.

    select M.NUMMER, M.ZEITPUNKT, K.NUMMER,  K.ZEITPUNKT 
    from  MESSWERT M, KORREKTURWERT K
    where K.NUMMER = (select max(NUMMER) 
                                  from KORREKTURWERT 
                                where NUMMER < M.NUMMER)
    
    
    
    

    Diese Abfrage liefert zwar die korrekten Werte, jedoch keine in der noch keine Korrekturwerte vorhanden sind. In Oracle kann ich jetzt einem Left Join mit =+ erreichen, in Sybase mit (+). In Access ist sowas offensichtlich nicht möglich. Auch nicht mit der JEFT JOIN ON Syntax.

    Zur Erklärung. In der Tabelle KORREKTUR stehen Korrekturwerte für die Messung und hier in NUMMER die M.NUMMER, ab dem der neue Korekturwert gültig ist.

    Ich hab mir im Augenblick mit einem UNION beholfen der die mit dem o.g. Query nicht gelieferten Zeilen anhängt. Da die gesamtabfrage natürlich noch einiges komplexer ist als im Beispiel, wierd das aber zu einem ziemlichen gefrickel.

    Gruß
    Wolfgang


    Dienstag, 8. November 2011 08:42

Antworten

  • Hallo Wolfgang,

    wie sieht es damit aus:

    SELECT M.NUMMER, 
    	M.ZEITWERT, 
    	K.NUMMER, 
    	K.ZEITWERT
    FROM  MESSWERT M 
    	LEFT JOIN KORREKTURWERT K 
    		ON M.NUMMER > K.NUMMER 
    WHERE K.NUMMER=(SELECT Max(NUMMER) 
    		FROM Korrekturwert AS K1 
    		WHERE K1.NUMMER < M.NUMMER) 
       OR K.NUMMER IS NULL
    


    Gruß Thomas

    Dienstag, 8. November 2011 14:17
  • Hallo Wolfgang,

    Wolfgang Zeiger wrote:

    das haut auch nicht hin. Jetzt fehlen wieder die Zeilen 1-4. Wie man es
    auch dreht und wendet. Access scheint hier keine Elegante Lösung zu
    bieten.

    Doch, meine urspruengliche Loesung ;-)

    SELECT M.NUMMER, M.ZEITPUNKT, K.NUMMER, K.ZEITPUNKT
    FROM  MESSWERT M LEFT JOIN KORREKTURWERT K ON M.NUMMER > K.NUMMER
    WHERE K.Nummer=(SELECT Max(Nummer) FROM Korrekturwert AS K1       WHERE K1.Nummer < M.Nummer) OR K.Nummer Is Null

    Gruss - Peter


    Mitglied im http://www.dbdev.org
    FAQ: http://www.donkarl.com

    Dienstag, 8. November 2011 14:19
    Moderator
  • Fast:

    SELECT  M.NUMMER ,
            M.ZEITPUNKT ,
            K.NUMMER
    FROM    MESSWERT M
            LEFT JOIN KORREKTURWERT K ON M.NUMMER >= K.NUMMER
    WHERE   K.Nummer IS NULL
            OR K.Nummer = ( SELECT  MAX(Nummer)
                            FROM    Korrekturwert AS K1
                            WHERE   K1.Nummer <= M.Nummer
                          ) ;
    


    Dienstag, 8. November 2011 14:20
    Moderator

Alle Antworten

  • Nachdem ich hier nicht raten möchte: Wo soll der JOIN den rein? Du kannst ihn gerne in Oracle-Syntax beschreiben.
    Dienstag, 8. November 2011 09:12
    Moderator
  • Hallo,

    Wolfgang Zeiger wrote:

    select M.NUMMER, M.ZEITPUNKT, K.NUMMER,    K.ZEITPUNKT
    from    MESSWERT M, KORREKTURWERT K
    where K.NUMMER = (select max(NUMMER)
                      from KORREKTURWERT
                      where NUMMER < M.NUMMER)
    

    Diese Abfrage liefert zwar die korrekten Werte, jedoch keine in der noch keine Korrekturwerte vorhanden sind.

    Probier mal:
    where K.NUMMER = (select max(NUMMER)
                      from KORREKTURWERT
                      where NUMMER < M.NUMMER)
    OR K.NUMMER Is Null

    Gruss - Peter


    Mitglied im http://www.dbdev.org
    FAQ: http://www.donkarl.com


    Dienstag, 8. November 2011 09:25
    Moderator
  • Hallo Stefan,

    Die Oracle Syntax wäre:

    select M.NUMMER, M.ZEITPUNKT, K.NUMMER,  K.ZEITPUNKT
    from  MESSWERT M, KORREKTURWERT K
    where K.NUMMER =* (select max(NUMMER)
                                  from KORREKTURWERT
                                where NUMMER < M.NUMMER)

    Gruß
    Wolfgang

    Dienstag, 8. November 2011 09:30
  • Hallo Peter,

    funktioniert leider auch nicht. Es fehlen die verfluchten Datensätze in denen noch kein Korrekturwert in der Tabelle Korrektur vorhanden waren und demnach nicht korrigiert werden müssen. Ich hab es inzwischen auch erfolglos mit dem Konstrukt versucht.

    and K.NUMMER = (IIF (select max(NUMMER)
                                      from KORREKTURWERT
                                    where NUMMER < M.NUMMER) > 0,
                                    select max(NUMMER)
                                      from KORREKTURWERT
                                    where NUMMER < M.NUMMER),
                                    NULL)

    Der gibt zwar keinen Fehler aber immer noch keine zusätzlichen Zeilen. :-)
    Boa, ih weiss schon warum ich lieber mit SQL-Server oder Oracle arbeite.

    Gruß
    Wolfgang

     

    Dienstag, 8. November 2011 10:35
  • Hallo zusammen,

    danke für Eure Mühe.
    Ich habe es jetzt so gelöst, das ich in die Tabelle "KORREKTUR" vor dem Query eine Zeile mit dem Eintrag K.NUMMER = 0 anlege.
    Ich wollte zwar nicht in diese Datenbank schreiben, aber sie lässt mir wohl keine Wahl. Bleibt die Frage offen, warum MS bei Access keine Left Joins mit Subquery zulässt, das in anderen Dialekten Standard ist.

    Gruß und Danke
    Wolfgang

    Dienstag, 8. November 2011 10:42
  • Hallo,

    Wolfgang Zeiger wrote:

    Ich habe es jetzt so gelöst, das ich in die Tabelle "KORREKTUR" vor dem
    Query eine Zeile mit dem Eintrag K.NUMMER = 0 anlege. Ich wollte zwar
    nicht in diese Datenbank schreiben, aber sie lässt mir wohl keine Wahl.
    Bleibt die Frage offen, warum MS bei Access keine Left Joins mit
    Subquery zulässt, das in anderen Dialekten Standard ist.

    Access kann sehr wohl mit Subqueries umgehen, nur war weder Stefan noch mir
    klar, wie die Tabellen in Verhaeltnis zueinander stehen, weil ein Join
    gefehlt hat. Falls du das kartesische Produkt ueber beide Tabellen haben
    willst, zeig mal anhand von Testdaten, welches Ergebnis du erwartest
    (Quelldaten aus beiden Tabellen und Ergebnis).

    Gruss - Peter


    Mitglied im http://www.dbdev.org
    FAQ: http://www.donkarl.com

    Dienstag, 8. November 2011 11:21
    Moderator
  • In dem Fall muss ich mich Peter anschließen. Kannst du uns ein kurzes Beispiel mit Daten geben? Denn von deiner BEschreibung ausgehend sollte es eigentlich Peters Lösung sein.
    Dienstag, 8. November 2011 12:31
    Moderator
  • Sehr stark vereinfacht....

    Tabelle MESSWERT:

    NUMMER, ZEITPUNKT, Wert, ETC...
    1          , Zeitstempel, Wert
    2          , Zeitstempel, Wert
    3          , Zeitstempel, Wert
    4          , Zeitstempel, Wert
    5          , Zeitstempel, Wert
    6          , Zeitstempel, Wert
    7          , Zeitstempel, Wert
    8          , Zeitstempel, Wert
    ...

    Tabelle KORREKTURWERT

    NUMMER, KORREKTURWERT
    4           , xyz
    18         , xyz
    518       , xyz
    9215     , xyz
    ...

    Erwünschtes Ergebnis

    M.NUMMER, M.ZEITSTEMPEL, M.WERT, K.KORREKTURWERT

    1                , Zeitstempel, Wert, NULL
    2                , Zeitstempel, Wert, NULL
    3                , Zeitstempel, Wert, NULL
    4                , Zeitstempel, Wert, NULL
    5                , Zeitstempel, Wert, xyz
    6                , Zeitstempel, Wert, xyz
    7                , Zeitstempel, Wert, xyz
    8                , Zeitstempel, Wert, xyz
    ...

    M.Nummer ist ein fortlaufender Messwert.
    K.Nummer ist die letzte M.NUMMER bevor ein neuer Korrekturwert wirksam wird.
    Ich verliere die Datensätze 1-4 wenn ich nicht vorher einen Datensatz mit K.NUMMER = 0 in die Tabelle KORREKTUR schreibe. Was ich eigentlich nicht wollte, da ich eigentlich nur lesend zugreifen wollte, da unter Umständen später nur Lesezugriff vorhanden ist. (MDB auf DVD, etc...)

    Gruß
    Wolfgang

    Dienstag, 8. November 2011 12:37
  • Wolfgang Zeiger wrote:

    Sehr stark vereinfacht....

    ... aber hilfreich. Stimmt die Annahme, dass Korrekturwert.Nummer 4 fuer
    alle Messwert.Nummer 5 bis 18 anzuzeigen ist, und dann von 19-518 der
    Korrekturwert aus Nummer 18?

    Das sollte dann eigentlich so aussehen:

    SELECT M.NUMMER, M.ZEITPUNKT, K.NUMMER,    K.ZEITPUNKT
    FROM  MESSWERT M LEFT JOIN KORREKTURWERT K ON M.NUMMER > K.NUMMER
    WHERE K.Nummer=(SELECT Max(Nummer) FROM Korrekturwert AS K1
    WHERE K1.Nummer < M.Nummer)

    Gruss - Peter


    Mitglied im http://www.dbdev.org
    FAQ: http://www.donkarl.com


    Dienstag, 8. November 2011 13:13
    Moderator
  • Ungetestet:

    SELECT  M.* ,
            K.*
    FROM    MESSWERT M
            LEFT JOIN KORREKTURWERT K ON K.NUMMER = ( SELECT   MAX(KI.NUMMER)
                                                       FROM     KORREKTURWERT KI
                                                       WHERE    M.NUMMER >= KI.NUMMER
                                                     ) ;
    


     

     

    Dienstag, 8. November 2011 13:19
    Moderator
  • Hallo Stefan,

    Stefan Hoffmann [MVP] wrote:

    Ungetestet:
    [...]
     LEFT JOIN KORREKTURWERT K ON K.NUMMER = ( SELECT  ...

    In Access wird der Subquery im Join nicht unterstuetzt. :-(

    Gruss - Peter


    Mitglied im http://www.dbdev.org
    FAQ: http://www.donkarl.com

    Dienstag, 8. November 2011 13:22
    Moderator
  • Hallo Peter,

    Deine Annahme stimmt. Aber die Query nicht... :-)
    Ab 19 bekomme ich so doppelte Zeilen und ab 518 dreifache etc. da das Produkt natürlich alle K.Nummern < M.NUMMER erfasst.

    Gruß
    Wolfgang

    Dienstag, 8. November 2011 13:29
  • Hallo,

    Wolfgang Zeiger wrote:

    Deine Annahme stimmt. Aber die Query nicht... :-)
    Ab 19 bekomme ich so doppelte Zeilen und ab 518 dreifache etc. da das Produkt natürlich alle K.Nummern < M.NUMMER erfasst.

    Schau nochmal, ich hab sie nachtraeglich korrigiert. So sollte sie
    aussehen:

    SELECT M.NUMMER, M.ZEITPUNKT, K.NUMMER, K.ZEITPUNKT
    FROM  MESSWERT M LEFT JOIN KORREKTURWERT K ON M.NUMMER > K.NUMMER
    WHERE K.Nummer=(SELECT Max(Nummer) FROM Korrekturwert AS K1       WHERE K1.Nummer < M.Nummer)

    Gruss - Peter


    Mitglied im http://www.dbdev.org
    FAQ: http://www.donkarl.com

    Dienstag, 8. November 2011 14:00
    Moderator
  • Hallo Peter,

    das haut auch nicht hin. Jetzt fehlen wieder die Zeilen 1-4. Wie man es auch dreht und wendet. Access scheint hier keine Elegante Lösung zu bieten.
    Aber noch mal vielen Dank für Eure Mühe.

    Gruß
    Wolfgang

    Dienstag, 8. November 2011 14:08
  • Hallo Wolfgang,

    wie sieht es damit aus:

    SELECT M.NUMMER, 
    	M.ZEITWERT, 
    	K.NUMMER, 
    	K.ZEITWERT
    FROM  MESSWERT M 
    	LEFT JOIN KORREKTURWERT K 
    		ON M.NUMMER > K.NUMMER 
    WHERE K.NUMMER=(SELECT Max(NUMMER) 
    		FROM Korrekturwert AS K1 
    		WHERE K1.NUMMER < M.NUMMER) 
       OR K.NUMMER IS NULL
    


    Gruß Thomas

    Dienstag, 8. November 2011 14:17
  • Hallo Wolfgang,

    Wolfgang Zeiger wrote:

    das haut auch nicht hin. Jetzt fehlen wieder die Zeilen 1-4. Wie man es
    auch dreht und wendet. Access scheint hier keine Elegante Lösung zu
    bieten.

    Doch, meine urspruengliche Loesung ;-)

    SELECT M.NUMMER, M.ZEITPUNKT, K.NUMMER, K.ZEITPUNKT
    FROM  MESSWERT M LEFT JOIN KORREKTURWERT K ON M.NUMMER > K.NUMMER
    WHERE K.Nummer=(SELECT Max(Nummer) FROM Korrekturwert AS K1       WHERE K1.Nummer < M.Nummer) OR K.Nummer Is Null

    Gruss - Peter


    Mitglied im http://www.dbdev.org
    FAQ: http://www.donkarl.com

    Dienstag, 8. November 2011 14:19
    Moderator
  • Fast:

    SELECT  M.NUMMER ,
            M.ZEITPUNKT ,
            K.NUMMER
    FROM    MESSWERT M
            LEFT JOIN KORREKTURWERT K ON M.NUMMER >= K.NUMMER
    WHERE   K.Nummer IS NULL
            OR K.Nummer = ( SELECT  MAX(Nummer)
                            FROM    Korrekturwert AS K1
                            WHERE   K1.Nummer <= M.Nummer
                          ) ;
    


    Dienstag, 8. November 2011 14:20
    Moderator
  • Hallo Stefan,

    Stefan Hoffmann [MVP] wrote:

    Fast:

    [...]
                    LEFT JOIN KORREKTURWERT K ON M.NUMMER >= K.NUMMER
    [...]
                                                    WHERE     K1.Nummer <= M.Nummer

    Damit bekommst du aber nicht das Ergebnis wie in Wolfgangs Beispiel:

    Bei ihm:

    3                , Zeitstempel, Wert, NULL
    4                , Zeitstempel, Wert, NULL
    5                , Zeitstempel, Wert, xyz

    Bei dir:

    3                , Zeitstempel, Wert, NULL
    4                , Zeitstempel, Wert, xyz
    5                , Zeitstempel, Wert, xyz

    Oder hab ich was falsch verstanden?

    Gruss - Peter


    Mitglied im http://www.dbdev.org
    FAQ: http://www.donkarl.com

    Dienstag, 8. November 2011 15:03
    Moderator
  • Hallo Thomas, Stefan und Peter,

    diese Ansätze funktionieren alle drei.
    Ich Danke Euch für die Hilfe.

    Gruß
    Wolfgang

    [Immer noch froh nicht allzu viel in Access machen zu brauchen]

    Dienstag, 8. November 2011 15:05
  • Hallo Peter,

    ich habe schon gesehen das sich der Stefan mit dem Einsprung in den nächsten Korrekturwert vertan hat. Das war kein Problem da es ja auf die Problematik des Subquerys keinen Einfluss hatte.

    Gruß
    Wolfgang

    Dienstag, 8. November 2011 15:15