CODEWITHSUNDEEP
sqlsql-server
ANJYR
ANJYR

Reputation: 2623

Convert nested Query to Join in SQL Server

I have a query

SELECT *  
FROM Stops 
WHERE CustomerID IN (SELECT ID FROM Customers WHERE Active = 1) 
  AND DriverID IS NOT NULL 
  AND TripID IN (SELECT ID 
                 FROM Trips 
                 WHERE ManagerID IN (SELECT ID FROM Users WHERE Active = 1) 
                   AND AssignedToID IN (SELECT ID FROM Users WHERE Active = 1) 
                   AND Modified > DATEADD(day, -60, GETDATE()))

I tried to convert to Join but I am stuck

SELECT *  
FROM Stops S  
JOIN Customers C ON C.ID = S.CustomerID
JOIN Trips T ON S.TripID = T.ID 
WHERE C.ACTIVE = 1
  AND S.DriverID IS NOT NULL
  AND T.Modified > DATEADD(day, -60, GETDATE())

Upvotes: 0

Views: 67

Answers (3)

jned29
jned29

Reputation: 478

I'm trying your second code on my end until I came up on the below code. You might try

SELECT *  
FROM Stops S  
JOIN Customers C ON C.ID = S.CustomerID AND C.ACTIVE = 1
JOIN Trips T ON S.TripID = T.ID AND T.Modified > DATEADD(day, -60, GETDATE())
LEFT JOIN Users U ON T.ManagerID = U.ID AND T.AssignedToID = U.ID
WHERE S.DriverID IS NOT NULL

What I usually do is to draw squares as tables and link them based on the requirements. Though, still not sure if my answer would work since I have no idea with what you are trying to achieve on your code aside from using JOIN.

Upvotes: 0

Souvik Nandi
Souvik Nandi

Reputation: 364

Using all joins, no nested queries

SELECT * FROM Stops A
INNER JOIN Customers B ON A.CustomerID = B.ID
INNER JOIN Trips C ON A.TripID = C.ID
INNER JOIN Users D ON C.ManagerID = D.ID
INNER JOIN Users E ON C.AssignedToID = E.ID
WHERE A.DriverID IS NOT NULL AND
B.Active = 1 AND
D.Active = 1 AND
E.Active = 1 AND
C.Modified > DATEADD(day, -60, GETDATE());

If you want unique data of stops you can also add "DISTINCT" to the select.

Upvotes: 3

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 31993

you can try like below subquery and join

SELECT S.*  FROM Stops S  
JOIN Customers C ON C.ID=S.CustomerID
join  (SELECT ID FROM Trips  where
            ManagerID IN (SELECT ID FROM Users WHERE Active = 1) AND 
            AssignedToID IN (SELECT ID FROM Users WHERE Active = 1) AND 
            Modified > DATEADD(day, -60, GETDATE())
       ) t on S.TripID=t.ID

Upvotes: 0

Related Questions