Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2003
    Posts
    311

    Unanswered: Nested SQL statements

    Im not great at sql and need to nest some statments (so they will run faster) i need to nest qryExsistingDeals into the qry below.. any help apreciated. thnks

    M~

    SELECT qryExistingDeals.Deal, qryExistingDeals.SumOfInvoiceAmount, qryExistingDeals.CheckNum, qryExistingDeals.Person,
    qryExistingDeals.Reference
    FROM qryExistingDeals LEFT JOIN
    Deal ON qryExistingDeals.Deal = Deal.Deal
    WHERE (((Deal.Deal) IS NULL));



    qryExistingDeals :
    SELECT Detail.Deal, Sum(Detail.InvoiceAmount) AS SumOfInvoiceAmount, Detail.CheckNum, Detail.Person, Detail.Reference
    FROM Detail
    GROUP BY Detail.Deal, Detail.CheckNum, Detail.Person, Detail.Reference
    ORDER BY Detail.Deal;

  2. #2
    Join Date
    Oct 2003
    Location
    Canada
    Posts
    574
    A nested query is of the form:

    select a.one, a.two from table a
    where a.one in (select b.one from table b);

    The main difference between your 2 queries is that the top one does not include Reference. To figure out how to do nested queries, decide what you are looking for in english first.

    I want these rows from this table where this field is in the qryExistingDeals query.

  3. #3
    Join Date
    Oct 2003
    Posts
    311
    hard to explain. I want the end result of the top qry, but i want to get it from 1 sql statement. If i have to leave reference out to to it i dont care.

    i am trying to find deals in the detail table that arent in the deal table can u help?

  4. #4
    Join Date
    Oct 2003
    Location
    Canada
    Posts
    574
    try something like this:

    select fields from detail right join deals on linkfield where deals.linkfield is null;

    the next question is do you need this to be updatable? If so, you may have to use a Not In

    select fields from detail where linkfield not in (select linkfield from deals)

Posting Permissions

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