WHERE " & strID & "" to get info from two tables 1

[gavray]

Hi,

I'm trying to use the strID to select info. from 2 tables. I've got a company table which lists only the companies details. Then I've got a jobs table which lists various jobs from these specif companies.

So Therefore I've got to try and link the companyID in the job table to the CompanyTable.ID.

I don't understand how to use strID and apply it to the other table, because strID only refers to the ID of one table.

This the error I get

Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'ID= 14 AND INNER JOIN CompanyTable.Company ON
JobTable.CompanyID = CompanyTable.ID'.

/displayjob2.asp, line 22

this is the code-

sSQL="SELECT JobTable.Salary"
sSQL=sSQL & " FROM JobTable"
sSQL=sSQL & " WHERE ID= " & strID & ""
sSQL=sSQL & " AND INNER JOIN CompanyTable.Company"
sSQL=sSQL & " ON JobTable.CompanyID = CompanyTable.ID"
sSQL=sSQL & " ORDER BY JobTable.Salary;"

Please can someone enlighten me,

Thanks,

Gavin

 

[Creeder]

Hi gavray,

The problem lies with you SQL statement

It should be something like

SELECT Jb.Salary
FROM jobTable AS JB
INNER JOIN CompanyTable AS Company
ON jb.CompanyId = company.companyid
WHERE ID = " & strID & "
ORDER BY jb.Salary

Hopes this help

YJ