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

    Unanswered: Identify columns with only one specific value

    To all-

    I couldn't think of a better title, but I believe that this is more complicated than it looks (although I'll be happily surprised if its actually easy):

    I have two columns in question:

    pkid order_id
    1 79
    2 79
    3 78
    4 77
    5 76
    6 77
    7 76

    What I want is to search for pkid with only 79:

    Desired results:
    pkid order_id
    1 79

    If I do the following:
    Select pkid where order_id = 79 then I get the following:

    pkid order_id
    1 79
    2 79

    I've played with distinct as well as group_by and I think I keep getting the same results.

    I've found a work around by creating a second table of everything that is <>79 and then joining (to find those with both) and then deleting from the original query... but that is a lot of steps and it seems like I'm not smart enough.

    I've also tried something like: select pkid where order_id=79 and order_id=<>79 but it just frowns at me and gives me nothing.

    Any guidance? Thank you all very much.
    Chris

  2. #2
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    Chris, First a question. How do you determine which ORDER_ID =79 row you want returned?

    As you know, there are 2 rows that qualify for that Where Clause:

    pkid order_id
    1 79
    2 79

    If you ALWAYS want the Minimum PKID, then it would be a simple

    SELECT MIN(PKID), ORDER_ID
    FROM table-name
    WHERE ORDER_ID = 79
    GROUP BY ORDER_ID

    If it is NOT always the Minimum, then there has to be something to determine which row you want. When you answer this question, you will have your answer.

  3. #3
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    What I want is to search for pkid with only 79
    As Stealth said, this condition doesn't exist. Is there something you left out of your question or in the examples?
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  4. #4
    Join Date
    Feb 2009
    Posts
    15
    You're right; I forgot a column in my example:

    pkid cust_id order_id
    1 100 79
    2 101 79
    3 101 78
    4 101 77
    5 101 76
    6 102 77
    7 102 76

    What I want is to search for pkid with only 79:

    Desired results:
    pkid cust_id order_id
    1 100 79

    If I do the following:
    Select pkid where order_id = 79 then I get the following:

    pkid cust_id order_id
    1 100 79
    2 101 79

    So what I need to select is the cust_id that has ONLY 79 and no 78, 77, etc.

    Is this possible?

    Thanks for your help.

    Chris

  5. #5
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    Chris, I will assume that generally you want the a specified Order Id for a single Customer Id where that Customer Id only has 1
    Code:
    SELECT PKID, CUST_ID, ORDER_ID
    FROM table-name
    WHERE CUST_ID IN (SELECT CUST_ID
                      FROM table-name
                      GROUP BY CUST_ID
                      HAVING COUNT(*) = 1
                     )
      AND ORDER_ID = 79
    This will find ALL rows in the table that have only 1 row per Customer Id and that row contains an Order Id = 79.

    If you want to filter it more, then you need more information

  6. #6
    Join Date
    Feb 2009
    Posts
    15
    That should be perfect! It's exactly what I needed. I'll try it soon. Thank you so very much!

Posting Permissions

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