Results 1 to 5 of 5
  1. #1
    PatrickKissane
    Guest

    Including a Subquery in SQL

    Hi Folks,

    I have a question regarding subqueries in SQL. I need to show in a query if one (or more) records from a table are referenced in a second table (as foreign keys).

    In the past, I have saved the 'sub' query as its own object, (ie Query2). I then treat it as a table in the Query Design Window as a basis of creating the 'main' query (ie Query1).

    Query1:
    ----------------------------------------------
    SELECT SignOffID, txtField1, IIf(IsNull([SubSignOffID]),False,True) AS ItemExists
    FROM Table1
    LEFT JOIN Query2 ON Table1.SignOffID = Query2.SubSignOffID

    Query2:
    ----------------------------------------------
    SELECT SignOffID AS SubSignOffID
    FROM Table2
    GROUP BY SignOffID;

    The output is a listbox-type thing, and I only want to know if a related record exists in Table2, not any of its details.

    What is the SQL syntax to include Query2 as a subquery inside Query1? I am using Access 97, but accessing the Jet backend with ADO 2.6 (ie no linked tables).

    The solution I can come up with involves two ADO Recordsets (1 & 2), and iterating through '1' searching for instances in '2'. Is there an easier way? Have I made it too complicated for myself?

    Cheers,

  2. #2
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    The Netherlands
    Posts
    216
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Including a Subquery in SQL

    SELECT SignOffID, txtField1, IIf(IsNull([SubSignOffID]),False,True) AS ItemExists
    FROM Table1
    WHERE SignOffID IN (SELECT SignOffID AS SubSignOffID
    FROM Table2);

  3. #3
    PatrickKissane
    Guest

    Re: Including a Subquery in SQL

    Thanks for your reply Bart,

    This goes part of the way, but limits the resulting recordset to ONLY items from Table1 that occur in Table2.
    I really need ALL items from Table1, and a field in the recordset telling me if one or more records exist in Table2. Any other ideas?

    Cheers,

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Including a Subquery in SQL

    Try using the exists keyword instead of IN.

    SELECT SignOffID, txtField1, exists(SELECT * FROM Table2 WHERE Table2.SignOffID=Table1.SignOffID) AS RecordExists
    FROM Table1;
    Charlotte

  5. #5
    PatrickKissane
    Guest

    Re: Including a Subquery in SQL

    Perfect! Thanks for your help, Charlotte.

    Cheers,

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •