Excluding duplicate rows - novice question

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

sean

Hi,

I am trying to exclude the duplicate rows from two tables. I am trying to
make the query so that the "options" table which contains all of the values
is compared against the "product_options" table which may contain only some
of the rows that the "options" table has. I would like to exclude all of the
rows which are contained in both tables. I have tried many different ways to
do this and I know that I am close, could someone help me out with some code
please!

Thanks in advance for your answer

Sean


PARAMETERS pProductID Long;
SELECT DISTINCT ([options].[OptionValue]), [product_options].[ProductID]
FROM options, product_options
WHERE [product_options].[ProductID]=pProductID AND
[options].[OptionValue]<>[product_options].[OptionValue];
 
Hi,

I am trying to exclude the duplicate rows from two tables. I am trying to
make the query so that the "options" table which contains all of the values
is compared against the "product_options" table which may contain only some
of the rows that the "options" table has. I would like to exclude all of the
rows which are contained in both tables. I have tried many different ways to
do this and I know that I am close, could someone help me out with some code
please!
Click to expand...

Your <> criterion is plausible but doesn't actually work: your current
query will find all records where ANY VALUE of OptionValue in the
Product_options table is unequal to the current OptionValue in
Options.

Instead, you need an exercize in pretzel logic that I call a
"Frustrated Outer Join" query:

PARAMETERS pProductID Long;
SELECT DISTINCT ([options].[OptionValue]),
[product_options].[ProductID]
FROM options LEFT JOIN product_options
ON [product_options].[ProductID]=pProductID AND
[options].[OptionValue]=[product_options].[OptionValue]
WHERE product_options.OptionValue IS NULL;

Taking this in steps - a simple JOIN would return those records that
*do* exist in both tables (exactly the records you *don't* want).
Changing the Join type (in SQL to LEFT JOIN, in the query grid by
selecting the join line and choosing Option 2 (or 3), "show all
records in Options and matching records in Product_Options", will do
just what it says, leaving those records in Options which don't have a
match returning NULL for all fields in ProductOptions. Applying a
criterion of IS NULL to one of those fields will then select only
those Options records which lack a match.
 
Hi john,

I tried your script but I get an error when I pasted the code into access
"join not supported" any other ideas?

Sean

John Vinson said:
Hi,

I am trying to exclude the duplicate rows from two tables. I am trying to
make the query so that the "options" table which contains all of the values
is compared against the "product_options" table which may contain only some
of the rows that the "options" table has. I would like to exclude all of the
rows which are contained in both tables. I have tried many different ways to
do this and I know that I am close, could someone help me out with some code
please!
Click to expand...

Your <> criterion is plausible but doesn't actually work: your current
query will find all records where ANY VALUE of OptionValue in the
Product_options table is unequal to the current OptionValue in
Options.

Instead, you need an exercize in pretzel logic that I call a
"Frustrated Outer Join" query:

PARAMETERS pProductID Long;
SELECT DISTINCT ([options].[OptionValue]),
[product_options].[ProductID]
FROM options LEFT JOIN product_options
ON [product_options].[ProductID]=pProductID AND
[options].[OptionValue]=[product_options].[OptionValue]
WHERE product_options.OptionValue IS NULL;

Taking this in steps - a simple JOIN would return those records that
*do* exist in both tables (exactly the records you *don't* want).
Changing the Join type (in SQL to LEFT JOIN, in the query grid by
selecting the join line and choosing Option 2 (or 3), "show all
records in Options and matching records in Product_Options", will do
just what it says, leaving those records in Options which don't have a
match returning NULL for all fields in ProductOptions. Applying a
criterion of IS NULL to one of those fields will then select only
those Options records which lack a match.
Click to expand...
 
Hi john,

I tried your script but I get an error when I pasted the code into access
"join not supported" any other ideas?
Click to expand...

Please post the actual SQL of your query.
 
Hi John

I basically pasted the existing code (that you wrote in your previous
message), here is the code that I am using below, with the error message
"join expression not support"

Sean

PARAMETERS pProductID Long;
SELECT DISTINCT (options.OptionValue),
product_options.ProductID
FROM options LEFT JOIN product_options
ON product_options.ProductID=pProductID AND
options.OptionValue=product_options.OptionValue
WHERE product_options.OptionValue IS NULL;
 
Hi John

I basically pasted the existing code (that you wrote in your previous
message), here is the code that I am using below, with the error message
"join expression not support"

Sean

PARAMETERS pProductID Long;
SELECT DISTINCT (options.OptionValue),
product_options.ProductID
FROM options LEFT JOIN product_options
ON product_options.ProductID=pProductID AND
options.OptionValue=product_options.OptionValue
WHERE product_options.OptionValue IS NULL;
Click to expand...

Ah. You can't join on a Parameter! You must join on a table field.
Since you aren't joining the Options table to the product_options
table by product, simply remove that field from the JOIN clause and
just join on OptionValue:

SELECT DISTINCT (options.OptionValue),
product_options.ProductID
FROM options LEFT JOIN product_options
ON options.OptionValue=product_options.OptionValue
WHERE product_options.OptionValue IS NULL;

Given that I have no idea what the intended function of pProductID
might have been, I'm not sure where or if it should be included.

Note that since you can be absolutely certain that the value of
product_options.ProductID will always be NULL in this query (since you
are asserting that the joining field in the LEFT JOIN is null, you're
selecting only the nonexistant records), it's not clear to me what the
purpose of this entire query might be!
 
Back
Top