Can anyone tell me what's wrong with this query?

HI,

I am creating a web license database for our team.

I was running along smoothy until I got to the point where I need to query three tables.  this is what I have.


This works....


<cfquery name="SoftwareList" datasource="softtrack">
SELECT *  
FROM tblDept INNER JOIN tblSoft ON tblDept.SoftID = tblSoft.SoftID  
WHERE DeptID=<cfqueryparam cfsqltype="CF_SQL_INTEGER"
       value="#url.DeptID#">
</cfquery>
---------------------------------------------------------------------------------------------------------------------------

BUT THIS DOES NOT....


<cfquery name="SoftwareList" datasource="softtrack">
SELECT *  
FROM tblDept INNER JOIN tblSoft ON tblDept.SoftID = tblSoft.SoftID INNER JOIN tblLicense ON tblDept.LicenseID = tblLicense.LicenseID
<!---This just passes from a hyperlink on the page before  ---->
<!---What they do is click on a department to see what software they have licensed.---->
WHERE DeptID=<cfqueryparam cfsqltype="CF_SQL_INTEGER"
       value="#url.DeptID#">
</cfquery>
---------------------------------------------------------------------------------------------------------------------------------
This is the error it throws.....

ODBC Error Code = 37000 (Syntax error or access violation)


[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'tblDept.SoftID = tblSoft.SoftID INNER JOIN tblLicense ON tblDept.LicenseID = tblLicense.LicenseID'.


SQL = "SELECT * FROM tblDept INNER JOIN tblSoft ON tblDept.SoftID = tblSoft.SoftID INNER JOIN tblLicense ON tblDept.LicenseID = tblLicense.LicenseID WHERE DeptID=?"

Query Parameter Value(s) -

Parameter #1 = 2

Data Source = "SOFTTRACK"

-------------------------------------------------------------------------------------------------------------------------------------

I know I will eventually need to query 4 tables, I even thought about creating a table called tblJunction that will hold multiple table ID's.  But I am not very good with SQL.

Can anyone help.


LVL 1
fixx17Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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.

jonnygo55Commented:
weird...I just experimented with sql in access...your query would have worked with sql server but it appears that you have to but parens around the first join...

SELECT *  
FROM (tblDept INNER JOIN tblSoft ON tblDept.SoftID = tblSoft.SoftID) INNER JOIN tblLicense ON tblDept.LicenseID = tblLicense.LicenseID...

I would try using the design view while creating queries...at least til you learn the syntax particular to it...
0

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
fixx17Author Commented:
Thanks, I took your advice and it worked.

Now when I create this database with access, I check the SQL, and it seems to have a lot of the  () syntax in it.   I wish I had access or an SQL client to practice with.  Maybe I can talk my boss into getting me the software and a test server to run on.

For now I will just try to use access to check the sql before putting it in the CFQUERY.

Thanks!


\m/....(-_-)....\m/

fixx
0
fixx17Author Commented:
Wierd, I just tried to give you credit and it said " you may not accept this answer"

What the heck!
0
fixx17Author Commented:
Oh, looks like it worked now.  

0
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
Web Servers

From novice to tech pro — start learning today.