|
 |
asp_databases thread: trouble with query
Message #1 by "list@o... on Wed, 26 Sep 2001 11:20:06 +0900
|
|
Hi,
I was hoping someone could help me with this query....
SELECT events.eventname, events.lang, events.datestart, events.dateEnd,
events.eventAddress, events.eventDescription, events.area, area.areaID,
area.areaname, designer.designerName, designer.designerID, eventDes.eventID,
eventDes.designerID
FROM EVENTS
LEFT OUTER JOIN eventDes ON events.eventID = eventDES.eventID
INNER JOIN Area ON events.area = area.areaID
INNER JOIN designers ON eventdes.designerID = designers.designerID
WHERE events.lang = '#lang#'
ORDER BY events.eventDateStart
I get this error
ODBC Error Code = 37000 (Syntax error or access violation)
[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in
query expression 'events.eventID = eventDES.eventID INNER JOIN Area ON
events.area = area.areaID INNER JOIN designers ON eventdes.designerID
designers.designerID'.
I was essentially trying to combine these 3 queries into 1.
SELECT *
FROM events
LEFT OUTER JOIN eventDes ON events.eventID = eventDES.eventID
WHERE events.lang = '#lang#'
ORDER BY events.eventDateStart
SELECT *
FROM eventdes, desingers
WHERE eventdes.designerID = designers.designerID
SELECT *
FROM events, area
WHERE events.area = area.areaID
Need to do a join with events and area to get the area name, and an outer
join to see if there is a designer associated with an event, and if there is
get their ID:s and then do a join with the designers table to get their
names.
Any help would be appreciated.
thanks
chad
Message #2 by "Drew, Ron" <RDrew@B...> on Wed, 26 Sep 2001 08:03:56 -0400
|
|
SELECT events.eventname, events.lang, events.datestart, events.dateEnd,
events.eventAddress, events.eventDescription, events.area, area.areaID,
area.areaname, designer.designerName, designer.designerID, eventDes.eventID,
eventDes.designerID
FROM events, area, eventDES, designers
WHERE events.eventID = eventDES.eventID
And events.area = area.areaID
And eventDES.designerID = designers.designerID
and events.lang = '#lang#'
ORDER BY events.eventDateStart
-----Original Message-----
From: list@o... [mailto:list@o...]
Sent: Tuesday, September 25, 2001 10:20 PM
To: ASP Databases
Subject: [asp_databases] trouble with query
Hi,
I was hoping someone could help me with this query....
SELECT events.eventname, events.lang, events.datestart, events.dateEnd,
events.eventAddress, events.eventDescription, events.area, area.areaID,
area.areaname, designer.designerName, designer.designerID, eventDes.eventID,
eventDes.designerID
FROM EVENTS LEFT OUTER JOIN eventDes ON events.eventID = eventDES.eventID
INNER JOIN Area ON events.area = area.areaID INNER JOIN designers ON
eventdes.designerID = designers.designerID
WHERE events.lang = '#lang#'
ORDER BY events.eventDateStart
I get this error
ODBC Error Code = 37000 (Syntax error or access violation)
[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in
query expression 'events.eventID = eventDES.eventID INNER JOIN Area ON
events.area = area.areaID INNER JOIN designers ON eventdes.designerID
designers.designerID'.
I was essentially trying to combine these 3 queries into 1.
SELECT *
FROM events
LEFT OUTER JOIN eventDes ON events.eventID = eventDES.eventID WHERE
events.lang = '#lang#' ORDER BY events.eventDateStart
SELECT *
FROM eventdes, desingers
WHERE eventdes.designerID = designers.designerID
SELECT *
FROM events, area
WHERE events.area = area.areaID
Need to do a join with events and area to get the area name, and an outer
join to see if there is a designer associated with an event, and if there is
get their ID:s and then do a join with the designers table to get their
names.
Any help would be appreciated.
thanks
chad
---
You are currently subscribed to asp_databases as: RDrew@B... To
unsubscribe send a blank email to $subst('Email.Unsub')
Message #3 by "list@o... on Thu, 27 Sep 2001 11:06:43 +0900
|
|
Hi...
thanks for your reply....
But that query gives me no results returned because there isnt any designers
associated with any events, however, it should show all events, and if there
is a designer associated display them too.
That is why I need to do an outer join.
Does anyone know the syntax to combine an outer join, and inner joins?
thanks
chad
> SELECT events.eventname, events.lang, events.datestart, events.dateEnd,
> events.eventAddress, events.eventDescription, events.area, area.areaID,
> area.areaname, designer.designerName, designer.designerID,
eventDes.eventID,
> eventDes.designerID
> FROM events, area, eventDES, designers
> WHERE events.eventID = eventDES.eventID
> And events.area = area.areaID
> And eventDES.designerID = designers.designerID
> and events.lang = '#lang#'
> ORDER BY events.eventDateStart
>
Message #4 by "Ken Schaefer" <ken@a...> on Thu, 27 Sep 2001 18:21:02 +1000
|
|
The syntax you had before looked OK.
Cut and paste it into Access QBE (open Access, choose Queries, choose "new",
switch to SQL view) and run it. It should tell you the position at which the
error is occuring.
Cheers
Ken
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
----- Original Message -----
From: "list@o..." <list@o...>
To: "ASP Databases" <asp_databases@p...>
Cc: <RDrew@B...>
Sent: Thursday, September 27, 2001 12:06 PM
Subject: [asp_databases] RE: trouble with query
: Hi...
:
: thanks for your reply....
: But that query gives me no results returned because there isnt any
designers
: associated with any events, however, it should show all events, and if
there
: is a designer associated display them too.
:
: That is why I need to do an outer join.
:
: Does anyone know the syntax to combine an outer join, and inner joins?
:
: thanks
: chad
:
:
: > SELECT events.eventname, events.lang, events.datestart, events.dateEnd,
: > events.eventAddress, events.eventDescription, events.area, area.areaID,
: > area.areaname, designer.designerName, designer.designerID,
: eventDes.eventID,
: > eventDes.designerID
: > FROM events, area, eventDES, designers
: > WHERE events.eventID = eventDES.eventID
: > And events.area = area.areaID
: > And eventDES.designerID = designers.designerID
: > and events.lang = '#lang#'
: > ORDER BY events.eventDateStart
: >
:
:
$subst('Email.Unsub')
Message #5 by David Cameron <dcameron@i...> on Thu, 27 Sep 2001 16:05:23 +1000
|
|
When I looked at your SELECT statement I could see nothing wrong with it.
When I ran it through query analyser there were no compile time errors. My
only guess is that a table or field name is wrong. Otherwise I can see
nothing wrong.
While Ron's answer may work it in not SQL92 compliant. This is not a huge
problem, as the syntax he has given you is supported by Access and SQL
Server, however those standards are there for a reason. If you want to go
with the syntax that Ron has given you there are ways of doing Outer joins
using that syntax. I used to have a ppt slideshow that had some examples of
syntax, but I seem to have deleted it. Someone else in the list may be able
to supply the syntax.
My suggestion is to go through and debug you original SQL statement. Add
joins one at a time and test it is you database until you find the error.
regards
David Cameron
nOw.b2b
dcameron@i...
-----Original Message-----
From: list@o... [mailto:list@o...]
Sent: Thursday, 27 September 2001 12:07 PM
To: ASP Databases
Cc: RDrew@B...
Subject: [asp_databases] RE: trouble with query
Hi...
thanks for your reply....
But that query gives me no results returned because there isnt any designers
associated with any events, however, it should show all events, and if there
is a designer associated display them too.
That is why I need to do an outer join.
Does anyone know the syntax to combine an outer join, and inner joins?
thanks
chad
> SELECT events.eventname, events.lang, events.datestart, events.dateEnd,
> events.eventAddress, events.eventDescription, events.area, area.areaID,
> area.areaname, designer.designerName, designer.designerID,
eventDes.eventID,
> eventDes.designerID
> FROM events, area, eventDES, designers
> WHERE events.eventID = eventDES.eventID
> And events.area = area.areaID
> And eventDES.designerID = designers.designerID
> and events.lang = '#lang#'
> ORDER BY events.eventDateStart
>
Message #6 by "list@o... on Thu, 27 Sep 2001 18:04:16 +0900
|
|
hi,
thanks for your response(s).....
So to clarify....
It looks like this syntax is correct?
FROM EVENTS
LEFT OUTER JOIN eventDes ON events.eventID = eventDES.eventID
INNER JOIN Area ON events.area = area.areaID
INNER JOIN designers ON eventdes.designerID = designers.designerID
This is my first query of this type, and I wasnt sure how to do it.
because I am going "FROM EVENTS" doesn that make events the central table?
Does it effect the rest of the query? (say if i did the order different)
I will keep on trying to play.
thanks
chad
>
> When I looked at your SELECT statement I could see nothing wrong with it.
> When I ran it through query analyser there were no compile time errors. My
> only guess is that a table or field name is wrong. Otherwise I can see
> nothing wrong.
>
> While Ron's answer may work it in not SQL92 compliant. This is not a huge
> problem, as the syntax he has given you is supported by Access and SQL
> Server, however those standards are there for a reason. If you want to go
> with the syntax that Ron has given you there are ways of doing Outer joins
> using that syntax. I used to have a ppt slideshow that had some examples
of
> syntax, but I seem to have deleted it. Someone else in the list may be
able
> to supply the syntax.
>
> My suggestion is to go through and debug you original SQL statement. Add
> joins one at a time and test it is you database until you find the error.
>
> regards
> David Cameron
> nOw.b2b
> dcameron@i...
>
> -----Original Message-----
> From: list@o... [mailto:list@o...]
> Sent: Thursday, 27 September 2001 12:07 PM
> To: ASP Databases
> Cc: RDrew@B...
> Subject: [asp_databases] RE: trouble with query
>
>
> Hi...
>
> thanks for your reply....
> But that query gives me no results returned because there isnt any
designers
> associated with any events, however, it should show all events, and if
there
> is a designer associated display them too.
>
> That is why I need to do an outer join.
>
> Does anyone know the syntax to combine an outer join, and inner joins?
>
> thanks
> chad
>
>
> > SELECT events.eventname, events.lang, events.datestart, events.dateEnd,
> > events.eventAddress, events.eventDescription, events.area, area.areaID,
> > area.areaname, designer.designerName, designer.designerID,
> eventDes.eventID,
> > eventDes.designerID
> > FROM events, area, eventDES, designers
> > WHERE events.eventID = eventDES.eventID
> > And events.area = area.areaID
> > And eventDES.designerID = designers.designerID
> > and events.lang = '#lang#'
> > ORDER BY events.eventDateStart
|
|
|
View Cart
