Convert nested Query to Join in SQL Server

Question

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())

@jned29 Yes, I want. I am confused, how I will use user table? because it comes twice in the nested query — ANJYR
– ANJYR, Commented Dec 14, 2018 at 5:52

Souvik Nandi · Accepted Answer · 2018-12-14 06:04:27Z

3

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.

answered Dec 14, 2018 at 6:04

Souvik Nandi

3642 silver badges6 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Zaynul Abadin Tuhin · Accepted Answer · 2018-12-14 06:04:22Z

0

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

edited Dec 14, 2018 at 6:04

answered Dec 14, 2018 at 5:53

Zaynul Abadin Tuhin

32.1k6 gold badges37 silver badges66 bronze badges

1 Comment

ANJYR Over a year ago

getting error - Incorrect syntax near the keyword 'IN'.

jned29 · Accepted Answer · 2018-12-14 06:26:23Z

0

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.

answered Dec 14, 2018 at 6:26

jned29

48912 silver badges51 bronze badges

Collectives™ on Stack Overflow

Convert nested Query to Join in SQL Server

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related