duplicate rows returned in query with inner join and where clause

  • Thread starter Thread starter JoD
  • Start date Start date
J

JoD

Using Access 2007

The format of the query is:
INSERT INTO tmpTable ( col1, col2 , col3 , col4, col5, col6, col7 )
SELECT CJ.col1, CJ.col2, CJ.col3, ER.col2, ER.col5, "val1", "val2"
FROM tbl1 AS CJ INNER JOIN tbl2 AS ER
ON ( (CJ.col3=ER.col1) AND
(ER.col6 = ("val3") AND ER.col1=[Enter number:]));

The first variation used a WHERE clause:
WHERE (ER.col6 = "val3" AND ER.col1=[Enter number:])

Either way, the query should return x rows to insert; instead it returns 2x
rows, with a duplicate for each row.

TIA for any assistance
 
Quite probably

SELECT CJ.col1, CJ.col2, CJ.col3, ER.col2, ER.col5, "val1", "val2"
FROM tbl1 AS CJ INNER JOIN tbl2 AS ER
ON ( (CJ.col3=ER.col1) AND
(ER.col6 = ("val3") AND ER.col1=[Enter number:]));


has twice the rows. Try SELECT DISTINCT ...


Hoping it may help,
Vanderghast, Access MVP
 
Actually, without "distinct" apparently a cartesian product is returned. If I
include the keyword "distinct", I still get back the duplicate rows.

Michel Walsh said:
Quite probably

SELECT CJ.col1, CJ.col2, CJ.col3, ER.col2, ER.col5, "val1", "val2"
FROM tbl1 AS CJ INNER JOIN tbl2 AS ER
ON ( (CJ.col3=ER.col1) AND
(ER.col6 = ("val3") AND ER.col1=[Enter number:]));


has twice the rows. Try SELECT DISTINCT ...


Hoping it may help,
Vanderghast, Access MVP


JoD said:
Using Access 2007

The format of the query is:
INSERT INTO tmpTable ( col1, col2 , col3 , col4, col5, col6, col7 )
SELECT CJ.col1, CJ.col2, CJ.col3, ER.col2, ER.col5, "val1", "val2"
FROM tbl1 AS CJ INNER JOIN tbl2 AS ER
ON ( (CJ.col3=ER.col1) AND
(ER.col6 = ("val3") AND ER.col1=[Enter number:]));

The first variation used a WHERE clause:
WHERE (ER.col6 = "val3" AND ER.col1=[Enter number:])

Either way, the query should return x rows to insert; instead it returns
2x
rows, with a duplicate for each row.

TIA for any assistance
Click to expand...
Click to expand...
 
Note that DISTINCT operates on the SELECTed expressions. I doubt that you
get duplicated rows IN THE RESULT of

SELECT DISTINCT CJ.col1, CJ.col2, CJ.col3, ER.col2, ER.col5, "val1",
"val2"
FROM tbl1 AS CJ INNER JOIN tbl2 AS ER
ON ( (CJ.col3=ER.col1) AND
(ER.col6 = ("val3") AND ER.col1=[Enter number:]));



ie, you won't get duplicted 5-tuples: {CJ.col1, CJ.col2, CJ.col3, ER.col2,
ER.col5}

If you get them duplicated after the insertion, probably it is because the
records where already present in the table, or because your code is executed
twice.



DISTINCT does not influence the nature of the join, unless your
installation is corrupted.



Vanderghast, Access MVP

JoD said:
Actually, without "distinct" apparently a cartesian product is returned.
If I
include the keyword "distinct", I still get back the duplicate rows.

Michel Walsh said:
Quite probably

SELECT CJ.col1, CJ.col2, CJ.col3, ER.col2, ER.col5, "val1", "val2"
FROM tbl1 AS CJ INNER JOIN tbl2 AS ER
ON ( (CJ.col3=ER.col1) AND
(ER.col6 = ("val3") AND ER.col1=[Enter number:]));


has twice the rows. Try SELECT DISTINCT ...


Hoping it may help,
Vanderghast, Access MVP


JoD said:
Using Access 2007

The format of the query is:
INSERT INTO tmpTable ( col1, col2 , col3 , col4, col5, col6, col7 )
SELECT CJ.col1, CJ.col2, CJ.col3, ER.col2, ER.col5, "val1", "val2"
FROM tbl1 AS CJ INNER JOIN tbl2 AS ER
ON ( (CJ.col3=ER.col1) AND
(ER.col6 = ("val3") AND ER.col1=[Enter number:]));

The first variation used a WHERE clause:
WHERE (ER.col6 = "val3" AND ER.col1=[Enter number:])

Either way, the query should return x rows to insert; instead it
returns
2x
rows, with a duplicate for each row.

TIA for any assistance
Click to expand...
Click to expand...
Click to expand...
 
Back
Top