duplicate rows returned in query with inner join and where clause

[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

 

[Michel Walsh]

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]

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

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

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

 

[Michel Walsh]

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

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

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

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