[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 758
  • Last Modified:

INNER JOIN problem

Hello,

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'

Thanks
0
guster5k
Asked:
guster5k
  • 2
1 Solution
 
LowfatspreadCommented:
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
SELECT *
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...
0
 
LowfatspreadCommented:
>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

e.g.
SELECT *
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

 
0

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now