Query Help to find multiple Values

• cdi_h

  Old Hand

  Points: 380

  More actions

  July 19, 2007 at 12:01 pm

  #117388

  I need help with a query . Example Data in the table is as given below.

  PRODUCTID  Value              

  1                20

  2                10

  3                20

  4                 15

  5                  8

  6                 9

  7                 10

  8                  20

  For each distinct "value " I would like to find and output which "Value" has more than one productID assigned.

  For the above given example, the query output should be as guve below because Value - 20 and 10 have more than one productid assigned.

  PRODUCTID  Value              

  1                20

  2                10

  3                20

  7                 10

  8                 20

  Please help me with this query.

   

  Thanks

• mrpolecat

  SSCertifiable

  Points: 7014

  More actions

  July 19, 2007 at 12:15 pm

  #720878

  select value from table

  group by value

  having count(productid) > 1

  ---

• SwePeso

  SSC-Dedicated

  Points: 39757

  More actions

  July 19, 2007 at 2:22 pm

  #720918

  SELECT d.PRODUCTID, d.Value FROM Table1 AS d

  INNER JOIN (SELECT Value  FROM Table1 GROUP BY Value HAVING COUNT(*) > 1) AS t ON t.Value = d.Value

   

  ---

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