Solved

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

Posted on 2003-11-06
4
189 Views
Last Modified: 2013-12-24
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
Comment
Question by:fixx17
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
4 Comments
 
LVL 2

Accepted Solution

by:
jonnygo55 earned 250 total points
ID: 9696640
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
 
LVL 1

Author Comment

by:fixx17
ID: 9697661
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
 
LVL 1

Author Comment

by:fixx17
ID: 9697675
Wierd, I just tried to give you credit and it said " you may not accept this answer"

What the heck!
0
 
LVL 1

Author Comment

by:fixx17
ID: 9697680
Oh, looks like it worked now.  

0

Featured Post

Create the perfect environment for any meeting

You might have a modern environment with all sorts of high-tech equipment, but what makes it worthwhile is how you seamlessly bring together the presentation with audio, video and lighting. The ATEN Control System provides integrated control and system automation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

One of the typical problems I have experienced is when you have to move a web server from one hosting site to another. You normally prepare all on the new host, transfer the site, change DNS and cross your fingers hoping all will be ok on new server…
What You Need to Know when Searching for a Webhost Provider
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial

697 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question