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

Posted on 2009-12-30
Last Modified: 2012-05-08
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.

WHERE [column2]=Table2.ID AND userId=@userId

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

I hope this makes sense?


Question by:Webbo_1980
    LVL 10

    Accepted Solution

    that is exactly what you have written

    in the sub-query Table3.Column2 will be compared with Table2.Column2 for a current row
    LVL 26

    Assisted Solution

    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
                SELECT NULL 
                FROM [Table3]
                WHERE [column2]=Table2.ID AND userId=@userId

    Open in new window


    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
    'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
    In this sixth video of the Xpdf series, we discuss and demonstrate the PDFtoPNG utility, which converts a multi-page PDF file to separate color, grayscale, or monochrome PNG files, creating one PNG file for each page in the PDF. It does this via a c…
    Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

    731 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now