help with sub select -urgent - please

  • Thread starter Thread starter sean
  • Start date Start date
S

sean

Hi There,

I am trying to select from a table where the particular id has duplicate
values, I would like to return only the values that are duplicated for that
particular id. When I run the statement on it's own I can return the results
without having the clause that accepts the Parameter, once I add the AND
clause in with the parameter the query returns all of the rows for that
particular id, rather than just to the duplicates.


Sean - Thanks in advance for your answer

!_------------------------- code

PARAMETERS pProductID Long;
SELECT OptionValue, ProductID
FROM product_options
WHERE product_options.ProductID=pProductID
AND OptionValue in
( select OptionValue
from product_options
group by OptionValue
having count(*) > 1 );

!---------------table data ProductID OptionValue
7 XL
7 L
7 M
7 S
7 XS
7 M
7 L



!-- results of query OptionValue ProductID
XL 7
L 7
M 7
S 7
M 7
L 7
 
Why not use the Find duplicates query wizard already availabl

----- sean wrote: ----

Hi There

I am trying to select from a table where the particular id has duplicat
values, I would like to return only the values that are duplicated for tha
particular id. When I run the statement on it's own I can return the result
without having the clause that accepts the Parameter, once I add the AN
clause in with the parameter the query returns all of the rows for tha
particular id, rather than just to the duplicates


Sean - Thanks in advance for your answe

!_------------------------- cod

PARAMETERS pProductID Long
SELECT OptionValue, ProductI
FROM product_option
WHERE product_options.ProductID=pProductI
AND OptionValue i
( select OptionValu
from product_option
group by OptionValu
having count(*) > 1 )

!---------------table data ProductID OptionValu
7 X
7
7
7
7 X
7
7



!-- results of query OptionValue ProductI
XL
L
M
S
M
L
 
I am trying to select from a table where the particular id has duplicate
values, I would like to return only the values that are duplicated for that
particular id. When I run the statement on it's own I can return the results
without having the clause that accepts the Parameter, once I add the AND
clause in with the parameter the query returns all of the rows for that
particular id, rather than just to the duplicates.
Click to expand...

You need the ID in the subquery to correlate it with the main query:

PARAMETERS pProductID Long;
SELECT OptionValue, ProductID
FROM product_options
WHERE product_options.ProductID=pProductID
AND OptionValue in
( select Z.OptionValue
from product_options AS Z
group by OptionValue
where Z.ProductID = Product_Options.ProductID
having count(*) > 1 );

This will return just the one set of dups where the ProductID is equal
to pProductID; if you want to see all the dups just leave off the
outer query's Where clause and the parameter.
 
Back
Top