LEFT JOIN doesn't work

  • Thread starter Thread starter Ivan Debono
  • Start date Start date
I

Ivan Debono

Hi all,

I have an Access 2000 mdb that I'm editing in Access 2003. I'm trying a
simple left outer join query in the form:
SELECT lefttable.id, righttable.id
FROM lefttable
LEFT JOIN righttable ON lefttable.id = righttable.lefttable.id

It doesn't work!!

I have 78 records in the lefttable and 41398 in the righttable. After
running the query I get a total on 414002... just 4 records more!!

I've upgraded the mdb to 2003 and I switched on/off the ANSI 92 option.
Datatypes match too.

I can't understand why it's not working!! Any ideas??

Thanks,
Ivan
 
I would guess that you might have duplicate values in the two tables. Are you
sure that LeftTable.ID is unique and RightTable.ID is unique?

Otherwise, I would suspect that you might have a corrupt index on one or both
tables. You could remove the indexes, compact the database, and try the query
again. If you get the expected results then re-establish the indexes and try again.

Another thing you can try is too use the DISTINCTROW operator and see what that
does for your results.
 
lefttable.id and righttable.id are AutoNumber fields and there's only the
PrimaryKey based on the id set. No other indexes.

I tried all the things you suggested and nothing works. This is one of the
simplest queries with 2 simple tables,and it doesn't want to work!!

Any more ideas?

Ivan
 
Ivan said:
I have an Access 2000 mdb that I'm editing in Access 2003. I'm trying a
simple left outer join query in the form:
SELECT lefttable.id, righttable.id
FROM lefttable
LEFT JOIN righttable ON lefttable.id = righttable.lefttable.id

It doesn't work!!

I have 78 records in the lefttable and 41398 in the righttable. After
running the query I get a total on 414002... just 4 records more!!
Click to expand...


That only implies that there are four records in the left
table that do not have any matching records in the right
table.

What else makes you think it isn't working.
 
Hmmm.. I guess you're right...

Marshall Barton said:
That only implies that there are four records in the left
table that do not have any matching records in the right
table.

What else makes you think it isn't working.
Click to expand...
 
Marshall Barton said:
That only implies that there are four records in the left
table that do not have any matching records in the right
table.

What else makes you think it isn't working.
Click to expand...

PMFBI

I've never seen this kind of join before

ON lefttable.id =** righttable.lefttable.id***

is this valid?

is that why Ivan gets 414002 instead of 78
which I would have thought with a "typical"
LEFT JOIN on 2 primary keys, i.e.,

SELECT lefttable.id, righttable.id
FROM lefttable
LEFT JOIN righttable
ON lefttable.id = righttable.id

with possibly 4 returned records
having righttable.id = NULL

I'm not sure I can even get my head
around this kind of construct.

gary
 
John,

Do you see how (if both "ids" were primary keys)
one would get more records than the number
of records in the "left" table?

gary
 
Left table has 78 records - four of them don't have a match in the right table,
so we get those four with a null for all the right table values. The other 74
match all the records in the right table, so we get 41,398 rows with matches and
4 additional with no matches.

The problem there is that rightable.ID CANNOT be unique and cannot, by
definition, be the primary key for the right table.

Gary said:
John,

Do you see how (if both "ids" were primary keys)
one would get more records than the number
of records in the "left" table?

gary

John Spencer said:
I think that is a typing error. At least I assumed it was.

Gary said:
:
Ivan Debono wrote:
I have an Access 2000 mdb that I'm editing in Access 2003. I'm trying a
simple left outer join query in the form:
SELECT lefttable.id, righttable.id
FROM lefttable
LEFT JOIN righttable ON lefttable.id = righttable.lefttable.id

It doesn't work!!

I have 78 records in the lefttable and 41398 in the righttable. After
running the query I get a total on 414002... just 4 records more!!


That only implies that there are four records in the left
table that do not have any matching records in the right
table.

What else makes you think it isn't working.

--
Marsh
MVP [MS Access]

PMFBI

I've never seen this kind of join before

ON lefttable.id =** righttable.lefttable.id***

is this valid?

is that why Ivan gets 414002 instead of 78
which I would have thought with a "typical"
LEFT JOIN on 2 primary keys, i.e.,

SELECT lefttable.id, righttable.id
FROM lefttable
LEFT JOIN righttable
ON lefttable.id = righttable.id

with possibly 4 returned records
having righttable.id = NULL

I'm not sure I can even get my head
around this kind of construct.

gary
Click to expand...
Click to expand...
Click to expand...
 
John,

You were very kind to post back for
something I just couldn't get past.

I realize now that the correction for the
"misspelling" was not

lefttable.id = righttable.id

but rather

lefttable.id = righttable.lefttableid

I apologise for wasting your time
with such a ludite moment. aaaahhggk!

and thanks,

gary

John Spencer said:
Left table has 78 records - four of them don't have a match in the right
table,
so we get those four with a null for all the right table values. The
other 74
match all the records in the right table, so we get 41,398 rows with
matches and
4 additional with no matches.

The problem there is that rightable.ID CANNOT be unique and cannot, by
definition, be the primary key for the right table.

Gary said:
John,

Do you see how (if both "ids" were primary keys)
one would get more records than the number
of records in the "left" table?

gary

John Spencer said:
I think that is a typing error. At least I assumed it was.

Gary Walter wrote:

:
Ivan Debono wrote:
I have an Access 2000 mdb that I'm editing in Access 2003. I'm
trying a
simple left outer join query in the form:
SELECT lefttable.id, righttable.id
FROM lefttable
LEFT JOIN righttable ON lefttable.id = righttable.lefttable.id

It doesn't work!!

I have 78 records in the lefttable and 41398 in the righttable.
After
running the query I get a total on 414002... just 4 records more!!


That only implies that there are four records in the left
table that do not have any matching records in the right
table.

What else makes you think it isn't working.

--
Marsh
MVP [MS Access]

PMFBI

I've never seen this kind of join before

ON lefttable.id =** righttable.lefttable.id***

is this valid?

is that why Ivan gets 414002 instead of 78
which I would have thought with a "typical"
LEFT JOIN on 2 primary keys, i.e.,

SELECT lefttable.id, righttable.id
FROM lefttable
LEFT JOIN righttable
ON lefttable.id = righttable.id

with possibly 4 returned records
having righttable.id = NULL

I'm not sure I can even get my head
around this kind of construct.

gary
Click to expand...
Click to expand...
Click to expand...
 
Back
Top