Sub Select Help

  • 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.

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 );
 
sean said:
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.

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 );
Click to expand...

Well, making some assumptions . . .

CREATE TABLE product_options
(ProductOptionsID INTEGER
,ProductID INTEGER
,OptionValue INTEGER
,CONSTRAINT pk_ProductOptionsID PRIMARY KEY (ProductOptionsID)
)

Sample Data
1, 1, 100
2, 1, 101
3, 2, 101
4, 3, 200
5, 4, 201
6, 4, 201
7, 4, 300
8, 6, 400
9, 1, 500

I copied and pasted the above query into Access 2000 (SP-3).

ProductID value 4 is the only one in the sample data with a duplicate
OptionValue.

I ran the query, typed in 4 to the prompt, and it produced only two rows,
those with the OptionValue values of 201. It did not return OptionValue
value 300.
 
Hi Chris,

When I run the query it gives me a different result, it displays some of the
duplicate rows and some of the non duplicates.

The table.....
ProductID OptionValue
7 XL
7 L
7 M
7 S
7 XS
7 M
7 L



The query result OptionValue ProductID
XL 7
L 7
M 7
S 7
M 7
L 7



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 );
 
Hmmm.

I'm running Access 2000 SP-3, JETSQL 4.0 SP-6

I altered the OptionValue data type to Char(2)

I deleted my original sample data, pasted in the new sample data, and re-ran
the query:

I typed in 7 at the prompt. The Query returned only the following Data:

L, 7
M, 7
M, 7
L, 7


Well, I'm not totally sure, so I chopped off the Primary Key column in my
version product_options (!!!), because that was my addition. I can't
imagine it would change anything, but . . .

No, that had no effect. The sample data was the same.

I rechecked the query's SQL-text as it is in my database, but no, I didn't
alter it in any way. I copied and pasted it directly into a QueryDef.
 
Back
Top