Solved

Need help with SQL Select statement

Posted on 2014-01-11
3
555 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 57

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 500 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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

830 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