Left Join Problem

  • Thread starter Thread starter Brian P. Mohr
  • Start date Start date
B

Brian P. Mohr

All,

I am having a problem showing all records on the Left Table.

SQL:

SELECT tblCurrentAccessories.[Version-Number], tblCurrentAccessories.Option,
tblCurrentAccessories_1.[Version-Number], tblCurrentAccessories_1.Option
FROM tblCurrentAccessories LEFT JOIN tblCurrentAccessories AS
tblCurrentAccessories_1 ON tblCurrentAccessories.Option =
tblCurrentAccessories_1.Option
GROUP BY tblCurrentAccessories.[Version-Number],
tblCurrentAccessories.Option, tblCurrentAccessories_1.[Version-Number],
tblCurrentAccessories_1.Option
HAVING (((tblCurrentAccessories.[Version-Number])="CIT Corporate Image
v21.8[26474AU]") AND
((tblCurrentAccessories_1.[Version-Number])="PGM-METRO[237375U]"));

-------------------
There are 28 records that match tblCurrentAccessories.Option. But there are
only 25 records that match tblCurrentAccessories.Option to
tblCurrentAccessories.Options_1.Option.

Only 25 records are displayed when the query is ran. I want all 28 records
from tblCurrentAccessories.Option to show and blank records to return on the
ones that do not match. I though that is what the LEFT JOIN would return.
What am I doing wrong?

Thanks in advance.

Brian P. Mohr
(e-mail address removed)
 
Dear Brian:

It appears you have here a query that is using grouping without having
implemented any aggregate functions. This is entirely reasonable to
do, but it has some definite side effects, one of which may be your
missing rows.

As a learning experiment, try the query without any GROUP BY clause,
and change the HAVING to WHERE:

SELECT tblCurrentAccessories.[Version-Number],
tblCurrentAccessories.Option,
tblCurrentAccessories_1.[Version-Number],
tblCurrentAccessories_1.Option
FROM tblCurrentAccessories LEFT JOIN tblCurrentAccessories AS
tblCurrentAccessories_1 ON tblCurrentAccessories.Option =
tblCurrentAccessories_1.Option
WHERE (((tblCurrentAccessories.[Version-Number])="CIT Corporate Image
v21.8[26474AU]") AND
((tblCurrentAccessories_1.[Version-Number])="PGM-METRO[237375U]"));

Does this produce the full 28 rows?

The grouping will eliminate duplication. Does that explain the 3
missing rows?

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
Tom,

Thanks for the help, but the query now produces nothing. I did figure out
how to do this in code with arrays. Thanks again.

Brian Mohr

Tom Ellison said:
Dear Brian:

It appears you have here a query that is using grouping without having
implemented any aggregate functions. This is entirely reasonable to
do, but it has some definite side effects, one of which may be your
missing rows.

As a learning experiment, try the query without any GROUP BY clause,
and change the HAVING to WHERE:

SELECT tblCurrentAccessories.[Version-Number],
tblCurrentAccessories.Option,
tblCurrentAccessories_1.[Version-Number],
tblCurrentAccessories_1.Option
FROM tblCurrentAccessories LEFT JOIN tblCurrentAccessories AS
tblCurrentAccessories_1 ON tblCurrentAccessories.Option =
tblCurrentAccessories_1.Option
WHERE (((tblCurrentAccessories.[Version-Number])="CIT Corporate Image
v21.8[26474AU]") AND
((tblCurrentAccessories_1.[Version-Number])="PGM-METRO[237375U]"));

Does this produce the full 28 rows?

The grouping will eliminate duplication. Does that explain the 3
missing rows?

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

All,

I am having a problem showing all records on the Left Table.

SQL:

SELECT tblCurrentAccessories.[Version-Number], tblCurrentAccessories.Option,
tblCurrentAccessories_1.[Version-Number], tblCurrentAccessories_1.Option
FROM tblCurrentAccessories LEFT JOIN tblCurrentAccessories AS
tblCurrentAccessories_1 ON tblCurrentAccessories.Option =
tblCurrentAccessories_1.Option
GROUP BY tblCurrentAccessories.[Version-Number],
tblCurrentAccessories.Option, tblCurrentAccessories_1.[Version-Number],
tblCurrentAccessories_1.Option
HAVING (((tblCurrentAccessories.[Version-Number])="CIT Corporate Image
v21.8[26474AU]") AND
((tblCurrentAccessories_1.[Version-Number])="PGM-METRO[237375U]"));

-------------------
There are 28 records that match tblCurrentAccessories.Option. But there are
only 25 records that match tblCurrentAccessories.Option to
tblCurrentAccessories.Options_1.Option.

Only 25 records are displayed when the query is ran. I want all 28 records
from tblCurrentAccessories.Option to show and blank records to return on the
ones that do not match. I though that is what the LEFT JOIN would return.
What am I doing wrong?

Thanks in advance.

Brian P. Mohr
(e-mail address removed)
Click to expand...
Click to expand...
 
Back
Top