Results 1 to 7 of 7
  1. #1
    Join Date
    Feb 2005
    Posts
    46

    Unanswered: Query Solution Needed

    Hi
    I have a table called items with one field called items and has 20 records in (simple)

    clean house
    put rubbish out
    check post
    ....

    I have a table called work which has 2 fields :
    work number
    item
    and it looks like this

    work number:2
    item:clean house

    work number:2
    itemut rubbish out

    I want two queries
    1. select all items for a specific work number (done) here its 2 records
    2. select all items but don't include the ones that have be selected for a specific job i.e the other 18 records can you help?

  2. #2
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,424
    Provided Answers: 8
    You want to be usiing inner joins and out joins

    so if you create the Query in design view right click the Black line and selct propreis and there are 1 of 3 options to pick from the the options
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    use an outer join with an IS NULL check

    this is not a homework site

    those aren't the real tables

    what other 18 records?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Feb 2005
    Posts
    46
    Nope i'm looking for <>

    i.e
    select all from table_list BUT NOT THOSE THAT ARE IN QUERY1

  5. #5
    Join Date
    Feb 2005
    Posts
    46
    r637

    There are 20 records in table_items
    query1 prompts for a work number I enter the number 2
    which returns 2 results

    query2 should select all items from table_items but not those found in query1

    does that make sense??

    never said it was a home work site I just changed the descriptions to make it easier to explain

  6. #6
    Join Date
    Feb 2005
    Posts
    46
    I found my solution

    SELECT * FROM table_list
    WHERE list_item NOT IN
    (
    SELECT work_detail
    FROM query1
    )

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    your solution is good

    what i was hinting at was this --

    SELECT table_list.* FROM table_list
    LEFT OUTER JOIN query1
    ON query1.work_detail = table_list.list_item
    WHERE query1.work_detail IS NULL
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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