CODEWITHSUNDEEP
sql
Giled
Giled

Reputation: 3

Avoiding a for loop in SQL

I have a table that I need to join with itself and find the missing items and I'm stuck. Maybe it's just an inexperience issue, but I seem to only be able to think in terms of a loop to accomplish what I want.

Essentially the table is as follows:

    packageID     Server
      1           baseline
      2           baseline
      3           baseline
      1           server1
      3           server1
      2           server2
      3           server2

What I'm hoping to accomplish is to find what packages are missing from each server. I can get this on a server by server basis like so:

    SELECT base.*, ISNULL(dp.server,'server1') as Server
    FROM (SELECT DISTINCT packageID FROM DB1
          WHERE server = 'baseline') base
    LEFT OUTER JOIN 
         (SELECT packageID, server FROM DB1
          WHERE server = 'server1') dp
   WHERE dp.server IS NULL

Which gives me what I want on an individual server basis.

    packageID     Server
       2          server1

How would I accomplish getting a list of each package a particular server is missing from the baseline?

Upvotes: 0

Views: 95

Answers (2)

user824910
user824910

Reputation: 1117

Try this scripts,

      DECLARE @DB1 TABLE
(
    PackageID INT
    ,Server VARCHAR(50)
)


INSERT INTO @DB1 VALUES(1,'baseline')
INSERT INTO @DB1 VALUES(2,'baseline')
INSERT INTO @DB1 VALUES(3,'baseline')
INSERT INTO @DB1 VALUES(1,'server1')
INSERT INTO @DB1 VALUES(3,'server1')
INSERT INTO @DB1 VALUES(2,'server2')
INSERT INTO @DB1 VALUES(3,'server2')

;WITH CTE_Package AS
(
    SELECT DISTINCT PackageID 
        , ROW_NUMBER() OVER(order by PackageID) AS ID
    FROM @DB1
)
,CTE_Servers AS
(
    SELECT DISTINCT Server 
        , ROW_NUMBER() OVER(order by PackageID) AS ID
    FROM @DB1
)
,CTE_Server_Packages AS
(
    SELECT DISTINCT P.PackageId, S.Server   
    FROM CTE_Servers S
    CROSS JOIN CTE_Package P    
)
SELECT Server, PackageID
FROM 
(
    SELECT SP.Server, SP.PackageID , D.PackageID [ActualPackage], D.Server [ActualServer]
    FROM CTE_Server_Packages SP
    LEFT JOIN @DB1 D
            ON D.PackageID = SP.PackageID
            AND D.Server = SP.Server
) AS TEMP
WHERE TEMP.ActualServer IS NULL

Upvotes: 0

pedromarce
pedromarce

Reputation: 5669

Assuming a view with the list of SERVERS, and take it as pseudo-SQL as I can't test it, something like this should work I think...

SELECT base.packageID, servers.server, 
             (SELECT count(*) 
                FROM base AS b1
               WHERE b1.packageId = base.packageId
                 AND b1.server    = servers.server) deployed
  FROM base, servers
 WHERE base.server = 'baseline'
   AND deployed = 0;

Upvotes: 1

Related Questions