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
Solved

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

Posted on 2003-11-06
4
188 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
  • 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

Easy, flexible multimedia distribution & control

Coming soon!  Ideal for large-scale A/V applications, ATEN's VM3200 Modular Matrix Switch is an all-in-one solution that simplifies video wall integration. Easily customize display layouts to see what you want, how you want it in 4k.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
how to add time taken to serve the request in ms in apache log configuration? 6 128
Firewall Speed Issue 6 69
Connect to MS-SQL server from Linux/PHP 8 89
WEB Farm 6 74
When it comes to showing a 404 error page to your visitors, you do not want that generic page to show, and you especially do not want your hosting provider’s ad error page to show either. In this article, I will show you how to enable the custom 40…
Lease-to-own eliminates the expenditure of hardware replacement and allows you to pay off the server over time. Usually, this is much cheaper than leasing servers. Think of lease-to-own as credit without interest.
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

856 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