Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2009
    Posts
    12

    Unanswered: how to query this?

    I have 2 tables which have a one-to-many relationship, and I want to query all records on the "one" side that don't have any corresponding records on the "many" side.

    How do I create a query that will find records that exist in one table but don't have a link in another table?

    I have a table called Requests, and another table called Transactions.

    Every record on the transactions table is linked to a request on the Requests table. The tables are linked by RequestID, which is the unique ID on the Request table (the Transactions table has a RequestID column, which shows the unique ID from the other table.)

    Now, I need to create a query that finds a RequestID that exists on the Requests table, but does not exist on the Transactions table.

    When I try to query both tables at the same time, it only pulls up records that exist on both tables. If a record only exists on one table, but there is no corresponding relationship to the other table, that record doesn't show up on the query.

    Is there some kind of criteria that I can write that will search for RequestID on the Request table and find those with no corresponding transacton on the Transaction table?

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Have a look at the EXISTS section here:
    SQL Tutorial [SELECT Statement Extended]
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    Or use an outer join.
    SELECT r.* FROM Requests AS r LEFT OUTER JOIN Transactions AS t ON r.RequestID = t.RequestID WHERE t.RequestID IS NULL;
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    That will work fine too.

    My own personal SQL coding rule:
    If you want to get data FROM a table, stick it in the FROM clause.
    If you want to check if data EXISTS in a table, but don't actually want to retrieve\ show any of the data, use EXISTS.

    I just like consistency - if a table is in my FROM clause then I know (barring errors) I am retrieving data from it.

    Not saying you are wrong and me right, just explaining why I answered EXISTS.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    Ah!

    I suppose it comes down to training and habit. I learned how to answer this kind of question by deconstructing the output of Access' Find Unmatched Wizard, so I've got into the habit of using outer joins. I've never really used subqueries. One more thing to learn!
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  6. #6
    Join Date
    Aug 2009
    Posts
    12
    thanks for steering me in the right direction, i read the link and figured it out

    (great link bythe way, in that an amateur like me could more or less understand it)

Posting Permissions

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