Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2003-11-06
4
Medium Priority
?
192 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 1000 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

Simplify Your Workload with One Tool

How do you combat today’s intelligent hacker while managing multiple domains and platforms? By simplifying your workload with one tool. With Lunarpages hosting through Plesk Onyx, you can:

Automate SSL generation and installation with two clicks
Experience total server control

Question has a verified solution.

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

In our day to day coding, how many times have we come across a necessity to check whether a URL is a broken link or not? For those of you that answered countless and are using ColdFusion like myself, then this article is for you.  It will show yo…
What You Need to Know when Searching for a Webhost Provider
Video by: ITPro.TV
In this episode Don builds upon the troubleshooting techniques by demonstrating how to properly monitor a vSphere deployment to detect problems before they occur. He begins the show using tools found within the vSphere suite as ends the show demonst…
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…
Suggested Courses

618 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