?
Solved

Need help with SQL Select statement

Posted on 2014-01-11
3
Medium Priority
?
562 Views
Last Modified: 2014-01-11
I created a select statement that displays fine when querying all records in my ColdFusion web page. When I input criteria in my search page I received an error.

I am using ColdFusion with MS Access.

Select Statement: (works fine with out any criteria inputted in the search)
SELECT *

FROM TBL_SHIP INNER JOIN (TBL_PROJECTS RIGHT JOIN TBL_DWG ON TBL_PROJECTS.PR_ID = TBL_DWG.DW_PROJECTID) ON TBL_SHIP.SH_ID = TBL_DWG.DW_SHIPID

Error received when inputting critera:
Error Executing Database Query.
[Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] JOIN expression not supported.
SQLSTATE         HY000
SQL          SELECT * FROM TBL_SHIP INNER JOIN (TBL_PROJECTS RIGHT JOIN TBL_DWG ON TBL_PROJECTS.PR_ID = TBL_DWG.DW_PROJECTID) ON TBL_SHIP.SH_ID = TBL_DWG.DW_SHIPID AND SH_HULL = 'YC' ORDER BY DW_GROUP  
0
Comment
Question by:DJPr0
  • 2
3 Comments
 
LVL 59

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 2000 total points
ID: 39773440
Your missing the verb WHERE in your SQL statement:

SELECT * FROM TBL_SHIP INNER JOIN (TBL_PROJECTS RIGHT JOIN TBL_DWG ON TBL_PROJECTS.PR_ID = TBL_DWG.DW_PROJECTID) ON TBL_SHIP.SH_ID = TBL_DWG.DW_SHIPID WHERE SH_HULL = 'YC' ORDER BY DW_GROUP  

Jim.
0
 

Author Comment

by:DJPr0
ID: 39773449
Thanks Jim - this seems to work:
SELECT *

FROM TBL_SHIP INNER JOIN (TBL_PROJECTS RIGHT JOIN TBL_DWG ON TBL_PROJECTS.PR_ID = TBL_DWG.DW_PROJECTID) ON TBL_SHIP.SH_ID = TBL_DWG.DW_SHIPID

Where 1=1

Not sure why I need 1=1 for the where to work
0
 

Author Closing Comment

by:DJPr0
ID: 39773643
Thx Jim.
0

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

Question has a verified solution.

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

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

592 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