Results 1 to 9 of 9
  1. #1
    Join Date
    Jun 2011
    Location
    Inside your mind
    Posts
    297

    Question Unanswered: SQL - Find two matching records in WHERE clause

    Hiya all,

    Title doesn't really give a lot away, which is probably why I'm having trouble finding examples on the net.

    Situation is this...

    I have a table, that has two columns (actually has more, but the others are irrelevant), namely - [Order Number] and [Product Code]

    I need to create a query that will return only the records that have a duplicate [Product Code] for the same [Order Number].


    For example:

    If my table, 'aTable' looks like this:

    Code:
     Order Number | Product Code
    --------------+--------------
         3333     |     WXYZ
                  |
         5555     |     ABCD
         5555     |     EFGH
         5555     |     WXYZ
                  |
         7777     |     WXYZ
         7777     |     WXYZ
                  |
         9999     |     ABCD
         9999     |     ABCD
         9999     |     EFGH
         9999     |     WXYZ
    I need the query to return the following 4 records:

    Code:
     7777 - WXYZ
     7777 - WXYZ
     9999 - ABCD
     9999 - ABCD
    I can't for the life of me work out how to do this with a query explicitly.

    Any help/explanations/solutions/suggestions would be extremely welcome.


    Thanks one and all.
    Looking for the perfect beer...

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    the 'find duplicates' query wizard should be able to identify the rows concerned
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    With 'TableX' being the name of the table, try:
    Code:
    SELECT TableX.[Order Number], 
           TableX.[Product Code]
    FROM   TableX INNER JOIN ( SELECT   TableX.[Order Number], 
                                        TableX.[Product Code]
                               FROM     TableX
                               GROUP BY TableX.[Order Number], 
                                        TableX.[Product Code]
                               HAVING ((Count(TableX.[Order Number]))>1) AND 
                                      ((Count(TableX.[Product Code]))>1)
                             ) AS A 
                  ON TableX.[Product Code] = a.[Product Code] AND 
                     TableX.[Order Number] = a.[Order Number]
    ;
    Have a nice day!

  4. #4
    Join Date
    Jun 2011
    Location
    Inside your mind
    Posts
    297
    This needs to be written as a query as part of an import script I'm writing...

    It's also not as simplified as the example I've given, there are a few WHERE EXISTS and WHERE NOT EXISTS going on in the query as well.

    I thought if I could get an answer to a simplified version I could adapt it to fit my more specific query.

    But yeah, it needs to happen when a user clicks a button, not via a wizard.
    Looking for the perfect beer...

  5. #5
    Join Date
    Jun 2011
    Location
    Inside your mind
    Posts
    297

    Thumbs up

    Thanks Sinn,

    I'm just finishing up at work, but will try this first thing and let you know how it works!

    Also @ Healdem, I don't need to remove the duplicates, I need to find them (as mentioned in a more advanced fashion), but instead of simply deleting them, I need to amalgamate, count and sum several bits of information from them. But as each Order is a seperate entity, then this is the scope I need to run it in. Thus why Sinn's looks hopeful as it's effectively creating a smaller table for just that order number (if I'm understanding that correctly?)]

    Thanks guys, I'll update tomorrow as to my findings!
    Looking for the perfect beer...

  6. #6
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome!
    Have a nice day!

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    why not enforce a unique index with a composite key of ordernumber and product code
    yes you would have to fix any current data problems. but once the key is define by defintion you cannot enter duplicates.

    incidentally the suggestion of using the Access wizard 'find duplicates wizard' does just that, it doens't delete rows it just finds rows with the same criteria
    I'd rather be riding on the Tiger 800 or the Norton

  8. #8
    Join Date
    Jun 2011
    Location
    Inside your mind
    Posts
    297
    Quote Originally Posted by healdem View Post
    why not enforce a unique index with a composite key of ordernumber and product code
    yes you would have to fix any current data problems. but once the key is define by defintion you cannot enter duplicates.
    Because duplicates [Product Code]s are allowed, and indeed need to be allowed for the same [Order Number].

    I need to find a list of all of those which appear however, and check various other details for that record against those held in 3 other tables, sum some fields, and delete specific records once all these checks have been made.

    The real issue is that the data that's being imported and rationalised for this database hasn't been input in a systematic way. So things like an [Order Number] having two [Product Codes], representing the same product is fine... So long as they have the same description as that held in the [Product Code] [Description] table...

    There are some cases where the [Order Number] has two [Product Code]s, that are the same, but a user in the past has manually changed the description, just for that order, rather than manually creating a new [Product Code], which should've been the case.

    My database is working (quite rightly) on the fact that a [Product Code] can only represent one specific product.

    The issue (and the cause of many a headache) is the fidelity of the source data I need to organise.

    The exception to the rule is that of the various bespoke product codes. The same [Product Code] can appear several times for the same [Order Number] with different [Descriptions], and this is fine. Another script will deal with organising those correctly... Thus why I mention the need for WHERE NOT EXISTS being incorporated into the query.


    Anyway, enough rambling.

    Works a treat Sinn. Thanks a lot! I think I understand what's going on it too, which is nice.
    Looking for the perfect beer...

  9. #9
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome, glad I could help
    Have a nice day!

Posting Permissions

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