Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2009
    Posts
    15

    Unanswered: selecting multiple but specific values

    To everyone,

    Happy Monday. I've been working through a series of queries over the weekend and have gotten stumped yet again with what is still probably a relatively simple solution.

    My sample DB is:

    pkid cust_id item_id
    1 100 79
    2 101 79
    3 101 78
    4 101 77
    5 102 78
    6 102 76
    7 103 76
    8 104 79
    9 104 76

    I'm trying to figure out the code to select cust_id where item_id is between 76 and 78 but not those with 79. My desired results would be:

    pkid cust_id item_id

    5 102 78
    6 102 76
    7 103 76

    I initially thought to create a table with all items between 76-78, then find all with those not between 76-78, join them, and then delete these items from the original table (which would leave only those between 76-78 without any other numbers). But this felt very labor intensive and unproductive... leading me to think there must be a better way.

    Anyone have some early morning guidance?

    Thank you very much
    Chris

  2. #2
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    Chris, you write it pretty much as you described it.

    Get rows where Item_id between 76 and 78 and Cust_id Not in (cust_Id where Item_id = 79)
    Code:
    SELECT
    FROM table-name
    WHERE Item_Id between 76 and 78
      AND Cust_id NOT IN(SELECT Cust_Id
                         FROM table-name
                         WHERE Item_Id = 79
                        )

  3. #3
    Join Date
    Feb 2009
    Posts
    15
    I feel dumb. It makes a lot of sense, the way you write it and it is accomplishing the exact same thing that I was doing with multiple tables.

    Thank you... very much.

    Chris

  4. #4
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    Don't worry about it. You actually have the hardest part down. Trying to state what you want to do clearly and completely is half the battle. After that it is just a matter of translating that to SQL syntax.

Posting Permissions

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