Post reply

To find invalid product codes

  • July 24, 2006 at 8:50 am

    #114306

    In Table 1, if column one data is produce Code information and column two data is the corresponding productname, please describe how you evaluate the data to determine if one product code has two productnames ( one of the product code's corresponding productname  is incorrect, keeping in mind there could be hundreds of thousands or millions of records.).

  • July 24, 2006 at 9:47 am

    #651112

    SELECT  Code,

      COUNT(*)

    FROM  Table

    GROUP BY Code

    HAVING  COUNT(*) > 1

    N 56°04'39.16"
    E 12°55'05.25"

  • July 24, 2006 at 10:08 am

    #651130

    Hi peter,

    thanks for your reply.

    the query gives the duplicate values in the table,

    but my task is to check against the codes table and list which are not valid product codes vs product name.

    thanks

    srinivas

     

     

     

  • July 24, 2006 at 10:44 am

    #651142

    Number 4 is an actual duplicate, (which I am 'assuming' your table allows), but the others have the same code, but slightly different names.  Actually, #3 is totally independent, so it does not show up in the output. 

    Do you have some sort of reference table to know if the ProductionDescription is correct?  Otherwise, how do you distinguish what is correct? 

    You may need to post some data and tell us what you believe is right and what is wrong and how you logically come to that decision...

     

    DECLARE @product TABLE( Code integer, 

                                         ProductDescription varchar(25))

    INSERT INTO @product

    SELECT 1, '1 This Thing' UNION ALL

    SELECT 1, '1 That Thing' UNION ALL

    SELECT 1, '1 The Other Thing' UNION ALL

    SELECT 2, '2 This Thing' UNION ALL

    SELECT 2, '2 That Thing' UNION ALL

    SELECT 3, '3 This Thing' UNION ALL

    SELECT 4, '4 This Thing' UNION ALL

    SELECT 4, '4 This Thing'

    SELECT P.Code, ProductDescription

    FROM @product P

       INNER JOIN( SELECT Code FROM @product GROUP BY Code HAVING  COUNT(*) > 1) C ON( P.Code = C. Code)

    I wasn't born stupid - I had to study.

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply