The Shed is going Social! Join us on FaceBook and Twitter and chime in on the conversation.
|
 |
|
Dev Shed Forums
> Databases
> MS SQL Development
|
sql syntax help please
Discuss sql syntax help please in the MS SQL Development forum on Dev Shed. sql syntax help please MS SQL Development forum discussing administration, MS SQL queries, and other MS SQL-related topics. SQL Server is Microsoft's enterprise database engine.
|
|
 |
|
|
|
|

Dev Shed Forums Sponsor:
|
|
|

September 5th, 2003, 10:33 AM
|
|
Junior Member
|
|
Join Date: Sep 2003
Posts: 1
Time spent in forums: < 1 sec
Reputation Power: 0
|
|
|
sql syntax help please
my connection is a remote dnsless ms access database.
i have four tables to query by way of sql.
i've got a cold fustion page using cfquery:
<CFQUERY NAME="The_Team_Summary" Datasource="#thisdatasource#"
username="this" PASSWORD="that">
SELECT tbl_TheTeams.TeamNumber AS Team_ID, tbl_TheTeams.TeamName AS Team_Name, Sum(tbl_TheData.TheSteps) AS XSteps, Count(tbl_LegWinner.TheWinner) AS Legs_Won
FROM tbl_LegWinner INNER JOIN (tbl_CurrentLeg INNER JOIN (tbl_TheTeams INNER JOIN tbl_TheData ON tbl_TheTeams.TeamNumber = tbl_TheData.TheTeam) ON tbl_CurrentLeg.txt_LegID = tbl_TheData.TheLegID) ON tbl_LegWinner.TheLeg = tbl_CurrentLeg.txt_LegID
GROUP BY tbl_TheTeams.TeamNumber, tbl_TheTeams.TeamName;
The connection itself works fine for simple one table queries.
When i cut and paste the sql from access, i have to edit the
code alittle bit: The FROM clause needs the path added to it like this: FROM 'D:\thepath\thedb.mdb' and the table name needs a (.)DOT added so i wind up with:
FROM 'D:\thepath\thedb.mdb'.thetablename (then the rest of the statement)...
My problem at the top of the page is i have a four tables, some groupon, some sum, some count. Cut & Paste SQL again needs to be edited, and I tried and failed to get it right.
Anyone know how to sytax several inner joins?
The access error message is:
Error Diagnostic Information
ODBC Error Code = 37000 (Syntax error or access violation)
[Microsoft][ODBC Microsoft Access Driver] Syntax error in FROM clause.
SQL = "SELECT tbl_TheTeams.TeamNumber AS Team_ID, tbl_TheTeams.TeamName AS Team_Name, Sum(tbl_TheData.TheSteps) AS XSteps, Count(tbl_LegWinner.TheWinner) AS Legs_Won FROM `D:\pathtodb\TheRace.mdb` tbl_LegWinner INNER JOIN (tbl_CurrentLeg INNER JOIN (tbl_TheTeams INNER JOIN tbl_TheData ON tbl_TheTeams.TeamNumber = tbl_TheData.TheTeam) ON tbl_CurrentLeg.txt_LegID = tbl_TheData.TheLegID) ON tbl_LegWinner.TheLeg = tbl_CurrentLeg.txt_LegID GROUP BY tbl_TheTeams.TeamNumber, tbl_TheTeams.TeamName"
Data Source = "thissource"
The error occurred while processing an element with a general identifier of (CFQUERY), occupying document position (3:1) to (4:36).
Thanks,
ddollar
|

September 5th, 2003, 06:48 PM
|
 |
SQL Consultant
|
|
Join Date: Feb 2003
Location: Toronto Canada
|
|
i've never had to use file paths, but from your description, where you say you have to stuff a dot between the path and the table name...
... you don't have a dot in the sql that's causing an error
you have
FROM `D:\pathtodb\TheRace.mdb` tbl_LegWinner
also, i wouldn't use those mysql-style backticks, i'd use singlequotes
plus, i's alias the table names
Code:
SELECT tbl_TheTeams.TeamNumber AS Team_ID
, tbl_TheTeams.TeamName AS Team_Name
, Sum(tbl_TheData.TheSteps) AS XSteps
, Count(tbl_LegWinner.TheWinner) AS Legs_Won
FROM 'D:\pathtodb\TheRace.mdb'.tbl_LegWinner W
INNER
JOIN (
tbl_CurrentLeg C
INNER
JOIN (
tbl_TheTeams T
INNER
JOIN tbl_TheData D
ON T.TeamNumber = D.TheTeam
)
ON C.txt_LegID = D.TheLegID
)
ON W.TheLeg = C.txt_LegID
GROUP BY tbl_TheTeams.TeamNumber
, tbl_TheTeams.TeamName
rudy
http://r937.com/
|

October 30th, 2003, 12:58 PM
|
|
Junior Member
|
|
Join Date: Oct 2003
Location: Central CT USA
Posts: 2
Time spent in forums: < 1 sec
Reputation Power: 0
|
|
In MS Access, try formatting your SQL syntax this way:
FROM
[D:\pathtodb\TheRace.mdb].tbl_LegWinner
ie, use square brackets, not single quotes, around your pathname.
Good luck!
--Brian

|
Developer Shed Advertisers and Affiliates
| Thread Tools |
Search this Thread |
|
|
|
| Display Modes |
Rate This Thread |
Linear Mode
|
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
|