• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 193
  • Last Modified:

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.


0
fixx17
Asked:
fixx17
  • 3
1 Solution
 
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
 
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

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

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