SQL Tabelle JOIN Problem

Question

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_

Answer 1

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

Answer 2

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.

Answer 3

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)

Answer 4

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.

Answer 5

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

Answer 6

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.

Answer 7

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.

Answer 8

Da dein "Maschinenstamm" ja die Haupttabelle ist, kannst du deine Ausschlüsse ganz einfach per Where-Klausel definieren.

Answer 9

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_