Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Westi on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

2 INNER JOINS ??

Status
Not open for further replies.

chenks

Programmer
Feb 21, 2004
1
GB
can anyone tell me that right way to do this ??

SELECT tblSchedule.fldDate, tblSchedule.fldSlot, tblSchedule.fldTimes, tblFilms.fldRating, tblFilms.fldReviewURL, tblSchedule.fldAddInfo
FROM tblSchedule
INNER JOIN tblFilms ON tblSchedule.fldSlot=tblFilms.fldTitle
INNER JOIN tblAdditionalInfo ON tblSchedule.fldAddInfo=tblAdditionalInfo.fldTitle
WHERE fldDate>=Date()
ORDER BY fldDate;

when i try to save this i get a syntax error.
"missing operator in query expression 'tblSchedule.fldSlot=tblFilms.fldTitle
INNER JOIN tblAdditionalInfo ON tblSchedule.fldAddInfo=tblAdditionalInfo.fldTitle' "
 
Possibly not working because of missing brackets, as illustrated below:
[tt]
INNER JOIN [red]([/red]tblFilms ON tblSchedule.fldSlot=tblFilms.fldTitle
INNER JOIN tblAdditionalInfo[red])[/red] ON tblSchedule.fldAddInfo=tblAdditionalInfo.fldTitle
[/tt]
Otherwise I Suggest you set it the query using the graphics query designer, then inspect the resulting SQL using the View, SQL View menu options. You can adapt your own SQL as required from this.

Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)
 
Building it in the query designer gives this different use of brackets:
Code:
SELECT tblSchedule.fldDate, tblSchedule.fldSlot, tblSchedule.fldTimes, tblFilms.fldRating, tblFilms.fldReviewURL, tblSchedule.fldAddInfo
FROM [COLOR=red]([/color]tblSchedule INNER JOIN tblFilms ON tblSchedule.fldSlot = tblFilms.fldTitle[COLOR=red])[/color] INNER JOIN tblAdditionalInfo ON tblSchedule.fldAddInfo = tblAdditionalInfo.fldTitle;
 
Hi chenks,

I don't think it matters which way round you have the parentheses in this case, but you do need some - there is no default behaviour. Each Join is between two explicit tabular objects; without the parentheses you don't have a second explicit object for the first Join, hence the message - it is trying to interpret the second join as part of the criteria for the first, and can't. So, both Steve's and cheerio's versions should give the same result - take your pick.

Enjoy,
Tony

------------------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading FAQ222-2244 before you ask a question.
 
Strictly the original posting was perfectly good SQL syntax.

Subject to changing the DATE() function to GETDATE() I would expect it to run fine on SQL Server, for example.

The real problem is that the JET engine in Access needs brackets to be supplied because of its own limitations in handling real SQL. The graphics query designer usually puts more than enough brackets to do the job.

Usually I would use the graphic designer unless I am trying to do something it just cannot handle such as a join on a <= operation or nested queries.
 
hi cheerio, ... sorry, but at what point did SQL Server get introduced into this discussion. It is not part of the problem domain, so other than possibly for comparison purposes (which at this point might simply cause confusion), it probably should not be discussed as part of the solution.



Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)
 
hi steve...sorry but I think cheerio's point is worthwhile. When you write an SQL query you are doing two things - writing SQL and writing Jet SQL. It is useful to know when those two things part company.

 
mike, whilst i agree with what cheerio is saying, my concern is that the poster is not using SQL Server, probably has never done so (there's no evidence from this thread that he is/has), and thus cheerio's response was not addressing the issue at hand,and in fact is taking the thread into 'foreign territory'). If I for example, now introduce Oracle SQL related syntax into this discussion, I may be adding something of interest to you, but I dont believe I'm addressing the core problem associated with this thread; ie. getting the poster's Access SQL to work.
Perhaps the poster can come back to us with some feedback.

Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)
 
The poster wrote some perfectly good SQL that Access cannot handle.

I think it is fair to tell him there is nothing wrong with his SQL skills - the fault is with Access with a JET engine that cannot handle standard SQL code.
 
ok; we'll agree to disagree to the approach to solving his problem. Is JET also at fault for not incorporating the GETDATE function? how far back to we take back the deltas between Access/Jet and SQL Server; all totally irrelavent to solving the initial question, though as I have stated, for someone with the appropriate background, the SQL Server related comments might make for interesting additional commentary.

Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top