webentwicklung-frage-antwort-db.com.de

SQL Server: Mehrere Tabellenverknüpfungen mit einer WHERE-Klausel

Ich verwende SQL Server und habe Schwierigkeiten, die Ergebnisse einer SELECT-Abfrage zu ermitteln, die ich möchte. Ich habe versucht, an verschiedenen Aufträgen teilzunehmen und Unterabfragen zu verwenden, aber nichts funktioniert so, wie ich es möchte. Nehmen Sie dieses gedachte Beispiel für Softwareanwendungen mit unterschiedlichen Versionsstufen, die möglicherweise auf Computern von Benutzern installiert werden. 

Ich muss eine JOIN mit einer WHERE ausführen, aber aus irgendeinem Grund kann ich nicht die gewünschten Ergebnisse erzielen. 

Vielleicht sehe ich meine Daten falsch an, ich bin mir nicht ganz sicher, warum ich das nicht schaffen kann.

Anwendungstabelle 

ID  Name
1   Word
2   Excel
3   PowerPoint

Software Tabelle (enthält Versionsinformationen für verschiedene Anwendungen) 

ID  ApplicationID   Version
1   1             2003
2   1             2007
3   2             2003
4   2             2007
5   3             2003
6   3             2007

Software_Computer - Verbindungstabelle 

ID  SoftwareID  ComputerID
1   1           1
2   4           1
3   2           2
4   5           2

Tabelle Computer 

ID  ComputerName
1   Name1
2   Name2

Ich möchte eine Abfrage, die ich ausführen kann, auf der ich einen bestimmten Computer auswähle, um anzuzeigen, welche Softwareversion und Anwendung installiert ist. Ich möchte jedoch auch, dass er anzeigt, welche Anwendung er nicht hat (die Version wäre eine NULL, da sie keine hat diese Software drauf)

SELECT Computer.ComputerName, Application.Name, Software.Version
FROM Computer
JOIN Software_Computer
    ON Computer.ID = Software_Computer.ComputerID
JOIN Software
    ON Software_Computer.SoftwareID = Software.ID
RIGHT JOIN Application
    ON Application.ID = Software.ApplicationID
WHERE Computer.ID = 1 

Ich möchte das folgende Ergebnis festlegen

ComputerName   Name          Version
Name1          Word          2003
Name1          Excel         2007
Name1          PowerPoint    NULL

Aber ich verstehe nur 

Results
ComputerName   Name          Version
Name1          Word           2003
Name1          Excel          2007

Ich dachte, der RIGHT JOIN würde alle Ergebnisse in die Anwendungstabelle aufnehmen, auch wenn sie nicht mit dem Computer verbunden sind. Was vermisse ich falsch?

16
Stormchao

Bei der Verwendung von LEFT JOIN oder RIGHT JOIN macht es einen Unterschied, ob Sie den Filter in die WHERE oder in die JOIN setzen.

Siehe diese Antwort auf eine ähnliche Frage, die ich vor einiger Zeit geschrieben habe:
Was ist der Unterschied zwischen diesen beiden Abfragen, wenn zwei unterschiedliche Ergebnissätze abgerufen werden?

Zusamenfassend:

  • wenn Sie es in die WHERE-Klausel schreiben (wie Sie es getan haben), werden die Ergebnisse, die diesem Computer nicht zugeordnet sind, vollständig herausgefiltert
  • wenn Sie es stattdessen in die Variable JOIN eingeben, werden die Ergebnisse, die diesem Computer nicht zugeordnet sind, im Abfrageergebnis nur mit den Werten von NULL angezeigt
    --> das ist was du willst
12

Die dritte erwartete Zeile (die mit PowerPoint) wird von der Computer.ID = 1-Bedingung ausgefiltert (versuchen Sie, die Abfrage mit dem Computer.ID = 1 or Computer.ID is null auszuführen, um zu sehen, was passiert).

Es wäre jedoch nicht sinnvoll, diese Bedingung zu löschen, denn wir wollen die Liste für einen bestimmten Computer.

Die einzige Lösung, die ich sehe, ist das Ausführen einer UNION zwischen Ihrer ursprünglichen Abfrage und einer neuen Abfrage, die die Liste der Anwendungen abruft, die auf dem Computer not gefunden sind. 

Die Abfrage könnte so aussehen:

DECLARE @ComputerId int
SET @ComputerId = 1

-- your original query
SELECT Computer.ComputerName, Application.Name, Software.Version
    FROM Computer
    JOIN dbo.Software_Computer
        ON Computer.ID = Software_Computer.ComputerID
    JOIN dbo.Software
        ON Software_Computer.SoftwareID = Software.ID
    RIGHT JOIN dbo.Application
        ON Application.ID = Software.ApplicationID
    WHERE Computer.ID = @ComputerId

UNION

-- query that retrieves the applications not installed on the given computer
SELECT Computer.ComputerName, Application.Name, NULL as Version
FROM Computer, Application
WHERE Application.ID not in 
    (
        SELECT s.ApplicationId
        FROM Software_Computer sc
        LEFT JOIN Software s on s.ID = sc.SoftwareId
        WHERE sc.ComputerId = @ComputerId
    )
AND Computer.id = @ComputerId
7

versuche dies

DECLARE @Application TABLE(Id INT PRIMARY KEY, NAME VARCHAR(20))
INSERT @Application ( Id, NAME )
VALUES  ( 1,'Word' ), ( 2,'Excel' ), ( 3,'PowerPoint' )
DECLARE @software TABLE(Id INT PRIMARY KEY, ApplicationId INT, Version INT)
INSERT @software ( Id, ApplicationId, Version )
VALUES  ( 1,1, 2003 ), ( 2,1,2007 ), ( 3,2, 2003 ), ( 4,2,2007 ),( 5,3, 2003 ), ( 6,3,2007 )

DECLARE @Computer TABLE(Id INT PRIMARY KEY, NAME VARCHAR(20))
INSERT @Computer ( Id, NAME )
VALUES  ( 1,'Name1' ), ( 2,'Name2' )

DECLARE @Software_Computer  TABLE(Id INT PRIMARY KEY, SoftwareId int, ComputerId int)
INSERT @Software_Computer ( Id, SoftwareId, ComputerId )
VALUES  ( 1,1, 1 ), ( 2,4,1 ), ( 3,2, 2 ), ( 4,5,2 )

SELECT Computer.Name ComputerName, Application.Name ApplicationName, MAX(Software2.Version) Version
FROM @Application Application 
JOIN @Software Software
    ON Application.ID = Software.ApplicationID
CROSS JOIN @Computer Computer
LEFT JOIN @Software_Computer Software_Computer
    ON Software_Computer.ComputerId = Computer.Id AND Software_Computer.SoftwareId = Software.Id
LEFT JOIN @Software Software2
    ON Software2.ID = Software_Computer.SoftwareID
WHERE Computer.ID = 1 
GROUP BY Computer.Name, Application.Name
2
Oleg Dok

Sie müssen einen LEFT JOIN ausführen.

SELECT Computer.ComputerName, Application.Name, Software.Version
FROM Computer
JOIN dbo.Software_Computer
    ON Computer.ID = Software_Computer.ComputerID
LEFT JOIN dbo.Software
    ON Software_Computer.SoftwareID = Software.ID
RIGHT JOIN dbo.Application
    ON Application.ID = Software.ApplicationID
WHERE Computer.ID = 1 

Hier ist die Erklärung:

Das Ergebnis eines linken äußeren Joins (oder einfach eines linken Joins) für Tabelle A und B enthält immer alle Datensätze der "linken" Tabelle (A), auch wenn die join-condition findet keinen passenden Datensatz in der "rechten" Tabelle (B). Dies bedeutet, dass, wenn die ON-Klausel mit 0 (Null) Datensätzen in B übereinstimmt, Der Join liefert immer noch eine Zeile im Ergebnis - jedoch mit NULL in jedem Spalte von B. Dies bedeutet, dass ein Left Outer Join alle .__-Werte zurückgibt. Werte aus der linken Tabelle plus übereinstimmende Werte aus der rechten Tabelle (oder NULL, falls kein übereinstimmendes Join-Prädikat vorhanden ist). Wenn die richtige Tabelle gibt eine Zeile zurück und die linke Tabelle gibt mehr als eine übereinstimmende Zeile zurück Dafür werden die Werte in der rechten Tabelle für jeden .__ wiederholt. eindeutige Zeile auf der linken Tabelle. Ab Oracle 9i wird LEFT OUTER Die JOIN-Anweisung kann ebenso wie (+) verwendet werden.

1
aF.
SELECT p.Name, v.Name
FROM Production.Product p
JOIN Purchasing.ProductVendor pv
ON p.ProductID = pv.ProductID
JOIN Purchasing.Vendor v
ON pv.BusinessEntityID = v.BusinessEntityID
WHERE ProductSubcategoryID = 15
ORDER BY v.Name;
1
SELECT Computer.Computer_Name, Application1.Name, Max(Soft.[Version]) as Version1
FROM Application1
inner JOIN Software
    ON Application1.ID = Software.Application_Id
cross join Computer
Left JOIN Software_Computer
    ON Software_Computer.Computer_Id = Computer.ID and Software_Computer.Software_Id = Software.Id
Left JOIN Software as Soft
    ON Soft.Id = Software_Computer.Software_Id
WHERE Computer.ID = 1 
GROUP BY Computer.Computer_Name, Application1.Name 
0

Versuchen Sie, dies funktioniert gut ....

SELECT computer.NAME, application.NAME,software.Version FROM computer LEFT JOIN software_computer ON(computer.ID = software_computer.ComputerID)
 LEFT JOIN software ON(software_computer.SoftwareID = Software.ID) LEFT JOIN application ON(application.ID = software.ApplicationID) 
 where computer.id = 1 group by application.NAME UNION SELECT computer.NAME, application.NAME,
 NULL as Version FROM computer, application WHERE application.ID not in ( SELECT s.applicationId FROM software_computer sc LEFT JOIN software s 
 on s.ID = sc.SoftwareId WHERE sc.ComputerId = 1 ) 
 AND computer.id = 1 
0
Gyan Singh

wählen Sie C.Computername, S.Version, A.Name von Computer C Inner Join Software_Computer SC auf C.Id = SC.ComputerId Inner Join Software S auf SC.SoftwareID = S.Id Inner Join Anwendung A auf S.ApplicationId = A.Id;

0
aman