none
SQL Tabelle JOIN Problem RRS feed

  • Soru

  • Hallo zusammen,

    ich habe ein Problem, dass ich nicht gelöst bekomme und ich hoffe ihr könnt mir eventuell paar Denkanstöße geben, um mein Problem zu lösen. Ich habe das Gefühl das es ein Verknüpfungs- bzw. Join Problem ist.

    Es sind zwei Tabellen die ich miteinander verknüpfen möchte. Maschinenstamm und die Aufträge.

    Das ganze ist grob so aufgebaut von mir:

    SELECT
         MaschID
         ,MaschBez
         ,SUM(Zyk)
         ,COUNT(Aufträge)
         ,MONTH(ProdEnde)
         ,YEAR(ProdEnde)  
    FROM 
         Maschinenstamm
         LEFT JOIN Aufträge ON (Maschinenstamm.MaschID=Aufträge.MaschID)
    WHERE
         MONTH(Aufträge.ProdEnde) = '12'
         AND YEAR(Aufträge.ProdEnde) = '2018'
    GROUP BY
          MaschID
         ,MaschBez
         ,MONTH(ProdEnde)
         ,YEAR(ProdEnde)
    

    Das Problem ist, dass jetzt nicht mehr alle Maschinen angezeigt werden. Sollte er nicht bei meinem LEFT JOIN die Maschinen trotzdem anzeigen, auch wenn die zu dem Monat / Jahr nicht benutzt worden sind?

    Oder sehe ich das Falsch? Ich bräuchte nämlich zu jedem Monat immer alle Maschinen auch wenn nichts gelaufen ist, müsste da einfach ein NULL Wert sein bei den Berechnungen.

    Mit einer WITH CTE hatte ich es auch probiert, jedoch macht es rein logisch kein Unterschied zu meinem oberen Vorgehen.

    Über Hilfe wäre ich sehr dankbar.

    Mit freundlichen Grüßen

    dash_


    19 Ağustos 2019 Pazartesi 07:44

Yanıtlar

  • Du willst alle Maschinen gelistet haben; die Eingrenzung der Daten geschieht aber auf der 'OUTER JOIN - Tabelle':

    SELECT
         MaschID
         ,MaschBez
         ,SUM(Zyk)
         ,COUNT(SEL1.MaschID)
         ,MONTH(ProdEnde)
         ,YEAR(ProdEnde)  
    FROM 
         Maschinenstamm
         LEFT JOIN (SELECT * FROM Aufträge WHERE MONTH(Aufträge.ProdEnde) = '12' AND YEAR(Aufträge.ProdEnde) = '2018') SEL1 ON (Maschinenstamm.MaschID=SEL1.MaschID)
    GROUP BY
          MaschID
         ,MaschBez
         ,MONTH(ProdEnde)
         ,YEAR(ProdEnde)

    19 Ağustos 2019 Pazartesi 09:18
  • Hi,

    letztendlich ist SEL1 ein Alias für die Unterabfrage im JOIN, damit man im restlichen SQL Statement mit diesem Aliasnamen auf die Daten der Unterabfrage zugreifen kann.


    Gruß, Stefan
    Microsoft MVP - Visual Developer ASP/ASP.NET (2001-2018)
    https://www.asp-solutions.de/ - IT Beratung, Softwareentwicklung, Remotesupport

    19 Ağustos 2019 Pazartesi 10:08
    Moderatör
  • Dies gehört generell zur Syntax von SQL, dass sog. "Derived Tables" oder eben Unterabfragen einen Namen haben müssen. Bei direkten Tabellen kann man eine Abkürzung als Alias definieren.

    Dasselbe machst du ja auch in der CTE, nur da gibt man den Namen direkt am Anfang an.

    Übrigens kann man im Join die Bedingung auch einfach ergänzen:

    LEFT JOIN Aufträge 
    on Maschinenstamm.MaschID=Aufträge.MaschID 
    AND mONTH(Aufträge.ProdEnde) = '12' 
    AND YEAR(Aufträge.ProdEnde) = '2018')
    

    Es gibt eben viele Wege in SQL.


    19 Ağustos 2019 Pazartesi 10:44
  • So habe meinen FEHLER gefunden.

    Ihr hattet Recht, beide Wege klappen.

    Habe mich heute morgen mit frischem Kopf nochmal hingesetzt und die Abfrage Schritt für Schritt geprüft.

    Habe euch nochmal hier meinen Denkfehler als Beispielquery erstellt, um meinen Fehler besser zu veranschaulichen, sollte jemand nochmal vor dem selben Problem sitzen wie ich und den selben Denkfehler haben.

    WITH [Maschinenstamm] ([MaschID],[MaschBez],[MaschStatus]) AS (
    SELECT 'Masch1', 'Maschine 1', 'Aktiv' UNION ALL
    SELECT 'Masch2', 'Maschine 2', 'Inaktiv' UNION ALL
    SELECT 'Masch3', 'Maschine 3', 'Aktiv' UNION ALL
    SELECT 'Masch4', 'Maschine 4', 'Aktiv' UNION ALL
    SELECT 'Masch5', 'Maschine 5', 'Aktiv' Union ALL
    SELECT 'Masch6', 'Maschine 6', 'Aktiv'
    ),
    [Aufträge] ([AuftragsNr],[MaschID],[ProdEnde],[Zyk],[Status]) AS (
    SELECT 'P001','Masch1',DATEADD(month,-1,GETDATE()),97,'Abgeschlossen' UNION ALL
    SELECT 'P002','Masch2',DATEADD(month,-2,GETDATE()),80,'Aktiv' UNION ALL
    SELECT 'P003','Masch5',DATEADD(month,-3,GETDATE()),82,'Abgeschlossen' UNION ALL
    SELECT 'P004','Masch3',DATEADD(month,-4,GETDATE()),81,'Abgeschlossen' UNION ALL
    SELECT 'P005','Masch1',DATEADD(month,-5,GETDATE()),65,'Abgeschlossen' UNION ALL
    SELECT 'P006','Masch2',DATEADD(month,-8,GETDATE()),70,'Abgeschlossen' UNION ALL
    SELECT 'P007','Masch2',DATEADD(month,-8,GETDATE()),87,'Abgeschlossen' UNION ALL
    SELECT 'P008','Masch3',DATEADD(month,-8,GETDATE()),99,'Abgeschlossen' UNION ALL
    SELECT 'P009','Masch4',DATEADD(month,-8,GETDATE()),87,'Aktiv' UNION ALL
    SELECT 'P010','Masch5',DATEADD(month,-8,GETDATE()),90,'Abgeschlossen' UNION ALL
    SELECT 'P011','Masch4',DATEADD(month,-8,GETDATE()),100,'Abgeschlossen' UNION ALL
    SELECT 'P012','Masch1',DATEADD(month,-9,GETDATE()),87,'Abgeschlossen' UNION ALL
    SELECT 'P013','Masch3',DATEADD(month,-1,GETDATE()),105,'Abgeschlossen' 
    )
    
    SELECT 
    	 [Maschinenstamm].[MaschID] AS 'Maschinen Nummer'
    	,[Maschinenstamm].[MaschBez] AS 'Maschinen Bezeichnung'
    	,[Maschinenstamm].[MaschStatus] AS 'Maschinen Status'
    	,MONTH([Aufträge].[ProdEnde]) AS 'Monat'
    	,YEAR([Aufträge].[ProdEnde]) AS 'Jahr'
    	,SUM([Aufträge].[Zyk]) AS 'Summe Zyk'
    	,COUNT([Aufträge].[AuftragsNr]) AS 'Anzahl Aufträge'
    	,SUM([Aufträge].[Zyk]) / COUNT([Aufträge].[AuftragsNr]) AS 'AVG Zyk'
    FROM 
    	 [Maschinenstamm]
    	 LEFT JOIN [Aufträge] ON ([Maschinenstamm].[MaschID]=[Aufträge].[MaschID] AND MONTH([Aufträge].[ProdEnde]) = '12' AND YEAR([Aufträge].[ProdEnde]) = '2018' AND [Aufträge].[Status] = 'Abgeschlossen')
    WHERE
    	 [Maschinenstamm].[MaschStatus] = 'Aktiv'
    	 --AND [Aufträge].[Status] = 'Abgeschlossen'		--FEHLERQUELLE muss in die JOIN Abfrage wie DATUM/JAHR
    GROUP BY
    	 [Maschinenstamm].[MaschID]
    	,[Maschinenstamm].[MaschBez]
    	,[Maschinenstamm].[MaschStatus]
    	,MONTH([Aufträge].[ProdEnde]) 
    	,YEAR([Aufträge].[ProdEnde])

    Vielen Dank für die schnelle und sehr gute Hilfe!

    Mit freundlichen Grüßen

    dash_

    20 Ağustos 2019 Salı 07:39

Tüm Yanıtlar

  • Um dies zu erreichen solltest du in der Whereklausel auch NULL-Werte zulassen, denn diese werden zur Zeit ausgeschlossen:

    WHERE
         MONTH(Aufträge.ProdEnde) = '12'
         AND YEAR(Aufträge.ProdEnde) = '2018'
         or Aufträge.ProdEnde is null


    19 Ağustos 2019 Pazartesi 08:05
  • Wenn ich das mache bekomme ich aber alle Maschinen doppelt angezeigt, mit jeweils dem Monat noch als NULL. Das hatte ich auch schon probiert.

    Das ist ja so nicht korrekt. Der zieht sich dann ja alle Aufträge ProdEnde auf Null stehen und nicht nur von diesem Monat Jahr. Von den eigentlich 100 Rows bekomme ich dadurch 190.

    Ich gruppiere ja schließlich auch nach dem Monat und Jahr, damit ich das in SSRS als Parameterfeld nutzen kann.


    • Düzenleyen dash_ 19 Ağustos 2019 Pazartesi 08:52
    19 Ağustos 2019 Pazartesi 08:41
  • Du willst alle Maschinen gelistet haben; die Eingrenzung der Daten geschieht aber auf der 'OUTER JOIN - Tabelle':

    SELECT
         MaschID
         ,MaschBez
         ,SUM(Zyk)
         ,COUNT(SEL1.MaschID)
         ,MONTH(ProdEnde)
         ,YEAR(ProdEnde)  
    FROM 
         Maschinenstamm
         LEFT JOIN (SELECT * FROM Aufträge WHERE MONTH(Aufträge.ProdEnde) = '12' AND YEAR(Aufträge.ProdEnde) = '2018') SEL1 ON (Maschinenstamm.MaschID=SEL1.MaschID)
    GROUP BY
          MaschID
         ,MaschBez
         ,MONTH(ProdEnde)
         ,YEAR(ProdEnde)

    19 Ağustos 2019 Pazartesi 09:18
  • Könntest du mir bitte kurz erklären wofür die SEL1 steht und wofür man die brauch?

    BZW was dein Code komplett bewirkt mit dem Join? Weil eins zu eins kann ich den scheinbar nicht übernehmen, da er mit dem SEL1 nichts anfangen kann und für mein Verständnis würde es sicher nicht schaden :P Kenne nur die Standard  Joins.

    19 Ağustos 2019 Pazartesi 09:58
  • Hi,

    letztendlich ist SEL1 ein Alias für die Unterabfrage im JOIN, damit man im restlichen SQL Statement mit diesem Aliasnamen auf die Daten der Unterabfrage zugreifen kann.


    Gruß, Stefan
    Microsoft MVP - Visual Developer ASP/ASP.NET (2001-2018)
    https://www.asp-solutions.de/ - IT Beratung, Softwareentwicklung, Remotesupport

    19 Ağustos 2019 Pazartesi 10:08
    Moderatör
  • Dies gehört generell zur Syntax von SQL, dass sog. "Derived Tables" oder eben Unterabfragen einen Namen haben müssen. Bei direkten Tabellen kann man eine Abkürzung als Alias definieren.

    Dasselbe machst du ja auch in der CTE, nur da gibt man den Namen direkt am Anfang an.

    Übrigens kann man im Join die Bedingung auch einfach ergänzen:

    LEFT JOIN Aufträge 
    on Maschinenstamm.MaschID=Aufträge.MaschID 
    AND mONTH(Aufträge.ProdEnde) = '12' 
    AND YEAR(Aufträge.ProdEnde) = '2018')
    

    Es gibt eben viele Wege in SQL.


    19 Ağustos 2019 Pazartesi 10:44
  • Also ich habe mal die Syntax eins zu eins von Herr Biegner übernommen, dass hat soweit funktionert. So wie ich es mir vorgestellt hatte, bloß ist mir gerade aufgefallen, dass eine Maschine auf Inaktiv gestellt ist. Diese würde ich gerne noch raus bekommen. Ist es dan überhaupt noch möglich? Im WHERE wird es nicht berücksichtigt und trotzdem noch angezeigt. In der Unterabfrage hatte ich es auch noch mit nem Join probiert und dort es dann zu filtern, leider auch ohne Erfolg.


    Die Syntax von bfuerchau hat leider nicht das gewünschte Ergebnis geliefert. Das war das selbe wie zuvor mit den nur 91 Rows anstelle von 100.

    Aber vielen Dank schonmal an alle Beteiligten. Habe heute wieder einiges lernen können.

    19 Ağustos 2019 Pazartesi 12:01
  • Da dein "Maschinenstamm" ja die Haupttabelle ist, kannst du deine Ausschlüsse ganz einfach per Where-Klausel definieren.
    19 Ağustos 2019 Pazartesi 14:53
  • So habe meinen FEHLER gefunden.

    Ihr hattet Recht, beide Wege klappen.

    Habe mich heute morgen mit frischem Kopf nochmal hingesetzt und die Abfrage Schritt für Schritt geprüft.

    Habe euch nochmal hier meinen Denkfehler als Beispielquery erstellt, um meinen Fehler besser zu veranschaulichen, sollte jemand nochmal vor dem selben Problem sitzen wie ich und den selben Denkfehler haben.

    WITH [Maschinenstamm] ([MaschID],[MaschBez],[MaschStatus]) AS (
    SELECT 'Masch1', 'Maschine 1', 'Aktiv' UNION ALL
    SELECT 'Masch2', 'Maschine 2', 'Inaktiv' UNION ALL
    SELECT 'Masch3', 'Maschine 3', 'Aktiv' UNION ALL
    SELECT 'Masch4', 'Maschine 4', 'Aktiv' UNION ALL
    SELECT 'Masch5', 'Maschine 5', 'Aktiv' Union ALL
    SELECT 'Masch6', 'Maschine 6', 'Aktiv'
    ),
    [Aufträge] ([AuftragsNr],[MaschID],[ProdEnde],[Zyk],[Status]) AS (
    SELECT 'P001','Masch1',DATEADD(month,-1,GETDATE()),97,'Abgeschlossen' UNION ALL
    SELECT 'P002','Masch2',DATEADD(month,-2,GETDATE()),80,'Aktiv' UNION ALL
    SELECT 'P003','Masch5',DATEADD(month,-3,GETDATE()),82,'Abgeschlossen' UNION ALL
    SELECT 'P004','Masch3',DATEADD(month,-4,GETDATE()),81,'Abgeschlossen' UNION ALL
    SELECT 'P005','Masch1',DATEADD(month,-5,GETDATE()),65,'Abgeschlossen' UNION ALL
    SELECT 'P006','Masch2',DATEADD(month,-8,GETDATE()),70,'Abgeschlossen' UNION ALL
    SELECT 'P007','Masch2',DATEADD(month,-8,GETDATE()),87,'Abgeschlossen' UNION ALL
    SELECT 'P008','Masch3',DATEADD(month,-8,GETDATE()),99,'Abgeschlossen' UNION ALL
    SELECT 'P009','Masch4',DATEADD(month,-8,GETDATE()),87,'Aktiv' UNION ALL
    SELECT 'P010','Masch5',DATEADD(month,-8,GETDATE()),90,'Abgeschlossen' UNION ALL
    SELECT 'P011','Masch4',DATEADD(month,-8,GETDATE()),100,'Abgeschlossen' UNION ALL
    SELECT 'P012','Masch1',DATEADD(month,-9,GETDATE()),87,'Abgeschlossen' UNION ALL
    SELECT 'P013','Masch3',DATEADD(month,-1,GETDATE()),105,'Abgeschlossen' 
    )
    
    SELECT 
    	 [Maschinenstamm].[MaschID] AS 'Maschinen Nummer'
    	,[Maschinenstamm].[MaschBez] AS 'Maschinen Bezeichnung'
    	,[Maschinenstamm].[MaschStatus] AS 'Maschinen Status'
    	,MONTH([Aufträge].[ProdEnde]) AS 'Monat'
    	,YEAR([Aufträge].[ProdEnde]) AS 'Jahr'
    	,SUM([Aufträge].[Zyk]) AS 'Summe Zyk'
    	,COUNT([Aufträge].[AuftragsNr]) AS 'Anzahl Aufträge'
    	,SUM([Aufträge].[Zyk]) / COUNT([Aufträge].[AuftragsNr]) AS 'AVG Zyk'
    FROM 
    	 [Maschinenstamm]
    	 LEFT JOIN [Aufträge] ON ([Maschinenstamm].[MaschID]=[Aufträge].[MaschID] AND MONTH([Aufträge].[ProdEnde]) = '12' AND YEAR([Aufträge].[ProdEnde]) = '2018' AND [Aufträge].[Status] = 'Abgeschlossen')
    WHERE
    	 [Maschinenstamm].[MaschStatus] = 'Aktiv'
    	 --AND [Aufträge].[Status] = 'Abgeschlossen'		--FEHLERQUELLE muss in die JOIN Abfrage wie DATUM/JAHR
    GROUP BY
    	 [Maschinenstamm].[MaschID]
    	,[Maschinenstamm].[MaschBez]
    	,[Maschinenstamm].[MaschStatus]
    	,MONTH([Aufträge].[ProdEnde]) 
    	,YEAR([Aufträge].[ProdEnde])

    Vielen Dank für die schnelle und sehr gute Hilfe!

    Mit freundlichen Grüßen

    dash_

    20 Ağustos 2019 Salı 07:39