Solved

Need help with SQL Select statement

Posted on 2014-01-11
3
558 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
[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
  • 2
3 Comments
 
LVL 58

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

Understanding Linux Permissions

Linux for beginners: How to view the permissions associated with files and directories and also how you can change them.

Question has a verified solution.

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

This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
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.

617 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