Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Warrington (Greater Manchester area)/Cheshire, UK
    Posts
    153
    Thanks
    0
    Thanked 0 Times in 0 Posts

    SQL Brainteaser (Access 97)

    Hi there all.

    I have a bit of an SQL brain teaser - I'm fairly sure that this can be done with in SQL, just I haven't been able to figure out how yet.

    I have two tables called AL_tbl_IDNumber and EmailAddressTbl, both of them have primary keys called IDNumber.

    I want to select all IDNumbers from AL_tbl_IDNumber that is NOT in EmailAddressTbl.

    Anyone got any good SQL solutions to this one?

    Thanks

    James

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

    Re: SQL Brainteaser (Access 97)

    SELECT AL_tbl_IDNumber.IDNumber
    FROM AL_tbl_IDNumber
    LEFT JOIN EmailAddressTbl
    ON AL_tbl_IDNumber.IDNumber = EmailAddressTbl.IDNumber
    WHERE (((EmailAddressTbl.IDNumber ) Is Null));
    Charlotte

  3. #3
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Warrington (Greater Manchester area)/Cheshire, UK
    Posts
    153
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL Brainteaser (Access 97)

    Thanks a million, Charlotte. It works

    These Left and Right and Outer Joins consistantly get me tied it knots which I can never undo! I can usually never figure them out without some help.

    James

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

    Re: SQL Brainteaser (Access 97)

    I'd suggest that you take advantage of the query grid and build them graphically to avoid getting tied into knots then. <img src=/S/grin.gif border=0 alt=grin width=15 height=15> Left and right joins are just outter joins. The direction indicates which side the determining table is on, that's all.
    Charlotte

  5. #5
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: SQL Brainteaser (Access 97)

    Charlotte answered your question, but FYI, when you go to create a new query, a pop-up box offers several choices, such as Design mode and Simple Query Wizard. ONe of the other choices is "Find UnMatched Query Wizard", which is what you want.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

Posting Permissions

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