How do i get the contents/Id of a of a field for a nested query if its contained in a parent query

I've got the following query i.e.

INSERT INTO Table1(value1,value2,value3)
      SELECT ID, * FROM Table2
      WHERE Column2 NOT IN
      (
            SELECT DISTINCT ID FROM [Table3]
            WHERE [column2]=Table2.ID AND userId=@userId
      )

The question is based around the nested contents i.e.

SELECT DISTINCT ID FROM [Table3]
WHERE [column2]=Table2.ID AND userId=@userId

Is there anyway for Table2.id to come from the "SELECT * FROM Table2" query listed above it?

I hope this makes sense?

Thanks
Webbo

Webbo_1980Asked:
Who is Participating?
 
lofCommented:
that is exactly what you have written

in the sub-query Table3.Column2 will be compared with Table2.Column2 for a current row
0
 
tigin44Commented:
your query does what you have described. For performance reason writing it in this way may be preferred.
INSERT INTO Table1(value1,value2,value3)
      SELECT ID, * 
      FROM Table2
      WHERE NOT EXISTS
      (
            SELECT NULL 
            FROM [Table3]
            WHERE [column2]=Table2.ID AND userId=@userId
      )

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.