How to implement Join for this below script for avoiding nested script

Question

SELECT UserDCID AS UserDCCompForDelCount1
FROM CDICentral.dbo.UserDCComponent AS udc
WHERE(
    UserDCID IN(
        SELECT UserDCID FROM CDICentral.dbo.UserDC AS ud WHERE (
            UserID NOT IN(
                SELECT UserID FROM CVPLocal.dbo.CSWSecurity AS cs
            )
        )
    )
)

Answer 1

Try this sql query

SELECT UserDCID AS UserDCCompForDelCount1 
FROM CDICentral.dbo.UserDCComponent AS udc 
INNER JOIN CDICentral.dbo.UserDC AS ud ON udc.UserDCID=ud.UserDCID
INNER JOIN CVPLocal.dbo.CSWSecurity AS cs ON ud.UserDCID=cs.UserID

Answer 2

Here is the query

SELECT UserDCID AS UserDCCompForDelCount1 FROM CDICentral.dbo.UserDCComponent AS udc 
INNER JOIN (
      SELECT UserDCID FROM CDICentral.dbo.UserDC AS ud 
      EXCEPT 
      SELECT UserID FROM CVPLocal.dbo.CSWSecurity AS cs
     )Table T
ON T.UserDCID = udc.UserDCID 

Answer 3

Let's start with the inner queries:

SELECT UserDCID FROM CDICentral.dbo.UserDC AS ud WHERE (
    UserID NOT IN(
        SELECT UserID FROM CVPLocal.dbo.CSWSecurity AS cs
    )
)

They select the rows from table ud that don't have a match in table cs, joining the tables using the field UserID which is present in both of them.

The query can be written as a LEFT JOIN:

SELECT ud.UserDCID
FROM CDICentral.dbo.UserDC AS ud
  LEFT JOIN CVPLocal.dbo.CSWSecurity AS cs
      ON ud.UserID = cs.UserID        # join the tables on common field `UserID`
WHERE cs.UserID IS NULL               # the row from `ud` has no match in `cs`

Back to the big query (I stripped the part that was refactored above because stays in the way for the explanation below):

SELECT UserDCID AS UserDCCompForDelCount1
FROM CDICentral.dbo.UserDCComponent AS udc
WHERE(
    UserDCID IN (
        SELECT UserDCID ...
        )
    )
)

This query selects the rows from udc that have matches in the result set produced by the inner query (the stripped one) when udc and the result set are joined by field UserDCID which is present in both.

This is a simple INNER JOIN (let's put the refactored inner query back):

SELECT udc.UserDCID AS UserDCCompForDelCount1
FROM CDICentral.dbo.UserDCComponent AS udc
  INNER JOIN CDICentral.dbo.UserDC AS ud
      ON udc.UserDCID = ud.UserDCID
  LEFT JOIN CVPLocal.dbo.CSWSecurity AS cs
      ON ud.UserID = cs.UserID
WHERE cs.UserID IS NULL

Let's read what the new query does: it selects UserDCComponents (udc) associated with UserDCs (ud) that don't have CSWSecurity (cs) records.