INNER JOIN problem


I'm using a access 2000 db and I'm trying to get information from 1 table and also a second table, if the second table does not have records related to the first table it's ok, just select the fields. But right now i'm getting this error if i use the 'cb.cd_lay = 0':

Microsoft OLE DB Provider for ODBC Drivers error '80004005'

[Microsoft][ODBC Microsoft Access Driver] Join expression not supported.

My SQL statement is: SELECT * FROM lays l INNER JOIN cont_box cb ON cb.cd_lay = 1 WHERE l.cd_lay = 1

if i use 'cb.cd_lay = l.cd_lay' then i get a empty rs, but the table lay has a record with the cd_lay value of '1'

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

is that a character or a numeric 1

technically they are different and should equate although access is notoriously forgiving in this area...

(by the way this is the MS SQL SERVER topic area, there is a specific area for MS Access questions)

have you tried
FROM lays l
INNER JOIN cont_box cb
 ON cb.cd_lay = l.cd_lay
 WHERE l.cd_lay = 1

you need to specify a join condition between the tables in the ON clause...

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
>if the second table does not have records related to the first table it's ok, just select the fields

for this condition you need an OUTER JOIN not an inner JOIN

FROM lays l
LEFT OUTER JOIN cont_box cb
 ON cb.cd_lay = l.cd_lay
 WHERE l.cd_lay = 1

then you'll see Lays data event if cont_box has no related data

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.