Problem with a SQL...

Hi,

I have this SQL
--------
SELECT main1.id_main,
main1.firstname,main1.lastname,class_ads.id_class_ad,
class_ads.title_class_ad,class_ads.date_posted,
class_subcategories.id_class_sub,class_subcategories.title_c lass_sub,class_categories.id_class_cat,class_categories.titl e_class_cat
FROM ((class_categories INNER JOIN class_subcategories ON
(class_categories.id_class_cat = class_subcategories.id_class_cat AND
class_categories.id_class_cat=4)) INNER JOIN (main1 INNER JOIN class_ads
ON ( main1.id_main = class_ads.id_main AND class_ads.id_cities = 89)))
ORDER BY date_posted desc;
-------

But I still get this error when I am trying to run it in an ASP page
(Access as database):
--------
Microsoft JET Database Engine (0x80040E14)
Syntax error in FROM clause
--------

As you see I am not using any reserved words here...so from what could
it be this error?

What wrong with my SQL?

Thanks.

Re: Problem with a SQL...

Mircea wrote:
> Hi,
>
> I have this SQL
> --------
> SELECT main1.id_main,
> main1.firstname,main1.lastname,class_ads.id_class_ad,
> class_ads.title_class_ad,class_ads.date_posted,
> class_subcategories.id_class_sub,class_subcategories.title_c lass_sub,class_categories.id_class_cat,class_categories.titl e_class_cat
> FROM ((class_categories INNER JOIN class_subcategories ON
> (class_categories.id_class_cat = class_subcategories.id_class_cat AND
> class_categories.id_class_cat=4)) INNER JOIN (main1 INNER JOIN class_ads
> ON ( main1.id_main = class_ads.id_main AND class_ads.id_cities = 89)))
> ORDER BY date_posted desc;
> -------
>
> But I still get this error when I am trying to run it in an ASP page
> (Access as database):
> --------
> Microsoft JET Database Engine (0x80040E14)
> Syntax error in FROM clause
> --------
>
> As you see I am not using any reserved words here...so from what could
> it be this error?
>
> What wrong with my SQL?
>
> Thanks.

The best way to put these kinds of queries together is to use the tool
provided by Access for doing so - the Query Builder. You can examine
the SQL that Access produces and match it against your freehand efforts
to see what differences (if any) there are between the two.

If you had done so, you would have seen that Access puts the criteria
in a WHERE clause, not as part of the JOIN conditions:

SELECT main1.id_main, main1.firstname, main1.lastname,
class_ads.id_class_ad, class_ads.title_class_ad, class_ads.date_posted,
class_subcategories.id_class_sub, class_subcategories.title_class_sub,
class_categories.id_class_cat, class_categories.title_class_cat FROM
((class_categories INNER JOIN class_subcategories ON (
class_categories.id_class_cat = class_subcategories.id_class_cat))
INNER JOIN (main1 INNER JOIN class_ads ON (main1.id_main =
class_ads.id_main ))) WHERE class_categories.id_class_cat=4 AND
class_ads.id_cities=89 ORDER BY date_posted desc;

--
Mike Brind

Re: Problem with a SQL...

Mike Brind wrote:
> Mircea wrote:
>> Hi,
>>
>> I have this SQL
>> --------
>> SELECT main1.id_main,
>> main1.firstname,main1.lastname,class_ads.id_class_ad,
>> class_ads.title_class_ad,class_ads.date_posted,
>> class_subcategories.id_class_sub,class_subcategories.title_c lass_sub,class_categories.id_class_cat,class_categories.titl e_class_cat
>> FROM ((class_categories INNER JOIN class_subcategories ON
>> (class_categories.id_class_cat = class_subcategories.id_class_cat AND
>> class_categories.id_class_cat=4)) INNER JOIN (main1 INNER JOIN class_ads
>> ON ( main1.id_main = class_ads.id_main AND class_ads.id_cities = 89)))
>> ORDER BY date_posted desc;
>> -------
>>
>> But I still get this error when I am trying to run it in an ASP page
>> (Access as database):
>> --------
>> Microsoft JET Database Engine (0x80040E14)
>> Syntax error in FROM clause
>> --------
>>
>> As you see I am not using any reserved words here...so from what could
>> it be this error?
>>
>> What wrong with my SQL?
>>
>> Thanks.
>
> The best way to put these kinds of queries together is to use the tool
> provided by Access for doing so - the Query Builder. You can examine
> the SQL that Access produces and match it against your freehand efforts
> to see what differences (if any) there are between the two.
>
> If you had done so, you would have seen that Access puts the criteria
> in a WHERE clause, not as part of the JOIN conditions:
>
> SELECT main1.id_main, main1.firstname, main1.lastname,
> class_ads.id_class_ad, class_ads.title_class_ad, class_ads.date_posted,
> class_subcategories.id_class_sub, class_subcategories.title_class_sub,
> class_categories.id_class_cat, class_categories.title_class_cat FROM
> ((class_categories INNER JOIN class_subcategories ON (
> class_categories.id_class_cat = class_subcategories.id_class_cat))
> INNER JOIN (main1 INNER JOIN class_ads ON (main1.id_main =
> class_ads.id_main ))) WHERE class_categories.id_class_cat=4 AND
> class_ads.id_cities=89 ORDER BY date_posted desc;
>
> --
> Mike Brind
>

I get the same error...unfortunately.
I wanted to use this SQL as model (it uses three tables...mine has four
tables):
------------
sql_comments="SELECT main1.id_main, main1.firstname,
events_comments.id_comments,events_comments.body_comments,ev ents_comments.date_posted,events_comments.time_posted,
images_people.filepath FROM ( main1 INNER JOIN events_comments ON (
main1.id_main = events_comments.id_main AND events_comments.id_event =
"& id_event &")) LEFT JOIN images_people ON (main1.id_main =
images_people.id_main AND images_people.mainpic = 1) ORDER BY
events_comments.date_posted desc,events_comments.time_posted desc"
-------------

The original query was this:
---------
sql_cat_all="SELECT class_ads.id_class_ad, class_ads.title_class_ad,
class_ads.posted_by, class_ads.id_main, class_ads.date_posted,
class_subcategories.id_class_sub,class_subcategories.title_c lass_sub,class_categories.id_class_cat,class_categories.titl e_class_cat
FROM (class_categories INNER JOIN class_subcategories ON
class_categories.id_class_cat = class_subcategories.id_class_cat) INNER
JOIN class_ads ON class_subcategories.id_class_sub =
class_ads.id_class_sub WHERE class_categories.id_class_cat=4 AND
class_ads.id_cities=89 ORDER BY date_posted desc"
---------

Which was working fine...the problem was that I wanted to have
"main1.firstname, main1.lastname" taken from the table "main1" (in the
original SQL above the field "class_ads.posted_by" held the firstname
and the lastname (and this is information redundancy).

So, at the original SQL I wanted to add a fourth table which holds the
firstname and lastname ("main1").

Thanks.

Re: Problem with a SQL...

Mircea wrote:
> Mike Brind wrote:
> > Mircea wrote:
> >> Hi,
> >>
> >> I have this SQL
> >> --------
> >> SELECT main1.id_main,
> >> main1.firstname,main1.lastname,class_ads.id_class_ad,
> >> class_ads.title_class_ad,class_ads.date_posted,
> >> class_subcategories.id_class_sub,class_subcategories.title_c lass_sub,class_categories.id_class_cat,class_categories.titl e_class_cat
> >> FROM ((class_categories INNER JOIN class_subcategories ON
> >> (class_categories.id_class_cat = class_subcategories.id_class_cat AND
> >> class_categories.id_class_cat=4)) INNER JOIN (main1 INNER JOIN class_ads
> >> ON ( main1.id_main = class_ads.id_main AND class_ads.id_cities = 89)))
> >> ORDER BY date_posted desc;
> >> -------
> >>
> >> But I still get this error when I am trying to run it in an ASP page
> >> (Access as database):
> >> --------
> >> Microsoft JET Database Engine (0x80040E14)
> >> Syntax error in FROM clause
> >> --------
> >>
> >> As you see I am not using any reserved words here...so from what could
> >> it be this error?
> >>
> >> What wrong with my SQL?
> >>
> >> Thanks.
> >
> > The best way to put these kinds of queries together is to use the tool
> > provided by Access for doing so - the Query Builder. You can examine
> > the SQL that Access produces and match it against your freehand efforts
> > to see what differences (if any) there are between the two.
> >
> > If you had done so, you would have seen that Access puts the criteria
> > in a WHERE clause, not as part of the JOIN conditions:
> >
> > SELECT main1.id_main, main1.firstname, main1.lastname,
> > class_ads.id_class_ad, class_ads.title_class_ad, class_ads.date_posted,
> > class_subcategories.id_class_sub, class_subcategories.title_class_sub,
> > class_categories.id_class_cat, class_categories.title_class_cat FROM
> > ((class_categories INNER JOIN class_subcategories ON (
> > class_categories.id_class_cat = class_subcategories.id_class_cat))
> > INNER JOIN (main1 INNER JOIN class_ads ON (main1.id_main =
> > class_ads.id_main ))) WHERE class_categories.id_class_cat=4 AND
> > class_ads.id_cities=89 ORDER BY date_posted desc;
> >
> > --
> > Mike Brind
> >
>
> I get the same error...unfortunately.
> I wanted to use this SQL as model (it uses three tables...mine has four
> tables):
> ------------
> sql_comments="SELECT main1.id_main, main1.firstname,
> events_comments.id_comments,events_comments.body_comments,ev ents_comments.date_posted,events_comments.time_posted,
> images_people.filepath FROM ( main1 INNER JOIN events_comments ON (
> main1.id_main = events_comments.id_main AND events_comments.id_event =
> "& id_event &")) LEFT JOIN images_people ON (main1.id_main =
> images_people.id_main AND images_people.mainpic = 1) ORDER BY
> events_comments.date_posted desc,events_comments.time_posted desc"
> -------------
>
> The original query was this:
> ---------
> sql_cat_all="SELECT class_ads.id_class_ad, class_ads.title_class_ad,
> class_ads.posted_by, class_ads.id_main, class_ads.date_posted,
> class_subcategories.id_class_sub,class_subcategories.title_c lass_sub,class_categories.id_class_cat,class_categories.titl e_class_cat
> FROM (class_categories INNER JOIN class_subcategories ON
> class_categories.id_class_cat = class_subcategories.id_class_cat) INNER
> JOIN class_ads ON class_subcategories.id_class_sub =
> class_ads.id_class_sub WHERE class_categories.id_class_cat=4 AND
> class_ads.id_cities=89 ORDER BY date_posted desc"
> ---------
>
> Which was working fine...the problem was that I wanted to have
> "main1.firstname, main1.lastname" taken from the table "main1" (in the
> original SQL above the field "class_ads.posted_by" held the firstname
> and the lastname (and this is information redundancy).
>
> So, at the original SQL I wanted to add a fourth table which holds the
> firstname and lastname ("main1").
>
> Thanks.

Have you tried using Access's Query Builder yet?

--
Mike Brind

Re: Problem with a SQL...

Mike Brind wrote:
> Mircea wrote:
>> Mike Brind wrote:
>>> Mircea wrote:
>>>> Hi,
>>>>
>>>> I have this SQL
>>>> --------
>>>> SELECT main1.id_main,
>>>> main1.firstname,main1.lastname,class_ads.id_class_ad,
>>>> class_ads.title_class_ad,class_ads.date_posted,
>>>> class_subcategories.id_class_sub,class_subcategories.title_c lass_sub,class_categories.id_class_cat,class_categories.titl e_class_cat
>>>> FROM ((class_categories INNER JOIN class_subcategories ON
>>>> (class_categories.id_class_cat = class_subcategories.id_class_cat AND
>>>> class_categories.id_class_cat=4)) INNER JOIN (main1 INNER JOIN class_ads
>>>> ON ( main1.id_main = class_ads.id_main AND class_ads.id_cities = 89)))
>>>> ORDER BY date_posted desc;
>>>> -------
>>>>
>>>> But I still get this error when I am trying to run it in an ASP page
>>>> (Access as database):
>>>> --------
>>>> Microsoft JET Database Engine (0x80040E14)
>>>> Syntax error in FROM clause
>>>> --------
>>>>
>>>> As you see I am not using any reserved words here...so from what could
>>>> it be this error?
>>>>
>>>> What wrong with my SQL?
>>>>
>>>> Thanks.
>>> The best way to put these kinds of queries together is to use the tool
>>> provided by Access for doing so - the Query Builder. You can examine
>>> the SQL that Access produces and match it against your freehand efforts
>>> to see what differences (if any) there are between the two.
>>>
>>> If you had done so, you would have seen that Access puts the criteria
>>> in a WHERE clause, not as part of the JOIN conditions:
>>>
>>> SELECT main1.id_main, main1.firstname, main1.lastname,
>>> class_ads.id_class_ad, class_ads.title_class_ad, class_ads.date_posted,
>>> class_subcategories.id_class_sub, class_subcategories.title_class_sub,
>>> class_categories.id_class_cat, class_categories.title_class_cat FROM
>>> ((class_categories INNER JOIN class_subcategories ON (
>>> class_categories.id_class_cat = class_subcategories.id_class_cat))
>>> INNER JOIN (main1 INNER JOIN class_ads ON (main1.id_main =
>>> class_ads.id_main ))) WHERE class_categories.id_class_cat=4 AND
>>> class_ads.id_cities=89 ORDER BY date_posted desc;
>>>
>>> --
>>> Mike Brind
>>>
>> I get the same error...unfortunately.
>> I wanted to use this SQL as model (it uses three tables...mine has four
>> tables):
>> ------------
>> sql_comments="SELECT main1.id_main, main1.firstname,
>> events_comments.id_comments,events_comments.body_comments,ev ents_comments.date_posted,events_comments.time_posted,
>> images_people.filepath FROM ( main1 INNER JOIN events_comments ON (
>> main1.id_main = events_comments.id_main AND events_comments.id_event =
>> "& id_event &")) LEFT JOIN images_people ON (main1.id_main =
>> images_people.id_main AND images_people.mainpic = 1) ORDER BY
>> events_comments.date_posted desc,events_comments.time_posted desc"
>> -------------
>>
>> The original query was this:
>> ---------
>> sql_cat_all="SELECT class_ads.id_class_ad, class_ads.title_class_ad,
>> class_ads.posted_by, class_ads.id_main, class_ads.date_posted,
>> class_subcategories.id_class_sub,class_subcategories.title_c lass_sub,class_categories.id_class_cat,class_categories.titl e_class_cat
>> FROM (class_categories INNER JOIN class_subcategories ON
>> class_categories.id_class_cat = class_subcategories.id_class_cat) INNER
>> JOIN class_ads ON class_subcategories.id_class_sub =
>> class_ads.id_class_sub WHERE class_categories.id_class_cat=4 AND
>> class_ads.id_cities=89 ORDER BY date_posted desc"
>> ---------
>>
>> Which was working fine...the problem was that I wanted to have
>> "main1.firstname, main1.lastname" taken from the table "main1" (in the
>> original SQL above the field "class_ads.posted_by" held the firstname
>> and the lastname (and this is information redundancy).
>>
>> So, at the original SQL I wanted to add a fourth table which holds the
>> firstname and lastname ("main1").
>>
>> Thanks.
>
> Have you tried using Access's Query Builder yet?
>
> --
> Mike Brind
>

Yes, I used it...but it adds two un-necessary tables to the query, two
more inner joins...

The query looks like this:
------------
SELECT main1.id_main, main1.firstname, maininfo.lastname,
class_categories.id_class_cat, class_categories.title_class_cat,
class_subcategories.id_class_sub, class_subcategories.title_class_sub,
class_ads.id_class_ad, class_ads.title_class_ad
FROM ((class_categories INNER JOIN class_subcategories ON
class_categories.id_class_cat = class_subcategories.id_class_cat) INNER
JOIN ((CITIES INNER JOIN class_ads ON CITIES.id_cities =
class_ads.id_cities) INNER JOIN ZIPCODES ON CITIES.id_cities =
ZIPCODES.id_cities) ON class_subcategories.id_class_sub =
class_ads.id_class_sub) INNER JOIN main1 ON ZIPCODES.id_zip = main1.id_zip;
----------

And I wanted to eliminate the un-necessary tables from this query
(tables which i am not using to extract data like CITIES and ZIPCODES).

Any other ideas based on this?

Thanks.

Re: Problem with a SQL...

> And I wanted to eliminate the un-necessary tables from this query

So, don't include those table in the Query Builder.

Bob Lehmann

"Mircea" wrote in message
news:etgXjDHcGHA.3712@TK2MSFTNGP03.phx.gbl...
> Mike Brind wrote:
> > Mircea wrote:
> >> Mike Brind wrote:
> >>> Mircea wrote:
> >>>> Hi,
> >>>>
> >>>> I have this SQL
> >>>> --------
> >>>> SELECT main1.id_main,
> >>>> main1.firstname,main1.lastname,class_ads.id_class_ad,
> >>>> class_ads.title_class_ad,class_ads.date_posted,
> >>>>
class_subcategories.id_class_sub,class_subcategories.title_c lass_sub,class_c
ategories.id_class_cat,class_categories.title_class_cat
> >>>> FROM ((class_categories INNER JOIN class_subcategories ON
> >>>> (class_categories.id_class_cat = class_subcategories.id_class_cat AND
> >>>> class_categories.id_class_cat=4)) INNER JOIN (main1 INNER JOIN
class_ads
> >>>> ON ( main1.id_main = class_ads.id_main AND class_ads.id_cities =
89)))
> >>>> ORDER BY date_posted desc;
> >>>> -------
> >>>>
> >>>> But I still get this error when I am trying to run it in an ASP page
> >>>> (Access as database):
> >>>> --------
> >>>> Microsoft JET Database Engine (0x80040E14)
> >>>> Syntax error in FROM clause
> >>>> --------
> >>>>
> >>>> As you see I am not using any reserved words here...so from what
could
> >>>> it be this error?
> >>>>
> >>>> What wrong with my SQL?
> >>>>
> >>>> Thanks.
> >>> The best way to put these kinds of queries together is to use the tool
> >>> provided by Access for doing so - the Query Builder. You can examine
> >>> the SQL that Access produces and match it against your freehand
efforts
> >>> to see what differences (if any) there are between the two.
> >>>
> >>> If you had done so, you would have seen that Access puts the criteria
> >>> in a WHERE clause, not as part of the JOIN conditions:
> >>>
> >>> SELECT main1.id_main, main1.firstname, main1.lastname,
> >>> class_ads.id_class_ad, class_ads.title_class_ad,
class_ads.date_posted,
> >>> class_subcategories.id_class_sub, class_subcategories.title_class_sub,
> >>> class_categories.id_class_cat, class_categories.title_class_cat FROM
> >>> ((class_categories INNER JOIN class_subcategories ON (
> >>> class_categories.id_class_cat = class_subcategories.id_class_cat))
> >>> INNER JOIN (main1 INNER JOIN class_ads ON (main1.id_main =
> >>> class_ads.id_main ))) WHERE class_categories.id_class_cat=4 AND
> >>> class_ads.id_cities=89 ORDER BY date_posted desc;
> >>>
> >>> --
> >>> Mike Brind
> >>>
> >> I get the same error...unfortunately.
> >> I wanted to use this SQL as model (it uses three tables...mine has four
> >> tables):
> >> ------------
> >> sql_comments="SELECT main1.id_main, main1.firstname,
> >>
events_comments.id_comments,events_comments.body_comments,ev ents_comments.da
te_posted,events_comments.time_posted,
> >> images_people.filepath FROM ( main1 INNER JOIN events_comments ON (
> >> main1.id_main = events_comments.id_main AND events_comments.id_event =
> >> "& id_event &")) LEFT JOIN images_people ON (main1.id_main =
> >> images_people.id_main AND images_people.mainpic = 1) ORDER BY
> >> events_comments.date_posted desc,events_comments.time_posted desc"
> >> -------------
> >>
> >> The original query was this:
> >> ---------
> >> sql_cat_all="SELECT class_ads.id_class_ad, class_ads.title_class_ad,
> >> class_ads.posted_by, class_ads.id_main, class_ads.date_posted,
> >>
class_subcategories.id_class_sub,class_subcategories.title_c lass_sub,class_c
ategories.id_class_cat,class_categories.title_class_cat
> >> FROM (class_categories INNER JOIN class_subcategories ON
> >> class_categories.id_class_cat = class_subcategories.id_class_cat) INNER
> >> JOIN class_ads ON class_subcategories.id_class_sub =
> >> class_ads.id_class_sub WHERE class_categories.id_class_cat=4 AND
> >> class_ads.id_cities=89 ORDER BY date_posted desc"
> >> ---------
> >>
> >> Which was working fine...the problem was that I wanted to have
> >> "main1.firstname, main1.lastname" taken from the table "main1" (in the
> >> original SQL above the field "class_ads.posted_by" held the firstname
> >> and the lastname (and this is information redundancy).
> >>
> >> So, at the original SQL I wanted to add a fourth table which holds the
> >> firstname and lastname ("main1").
> >>
> >> Thanks.
> >
> > Have you tried using Access's Query Builder yet?
> >
> > --
> > Mike Brind
> >
>
> Yes, I used it...but it adds two un-necessary tables to the query, two
> more inner joins...
>
> The query looks like this:
> ------------
> SELECT main1.id_main, main1.firstname, maininfo.lastname,
> class_categories.id_class_cat, class_categories.title_class_cat,
> class_subcategories.id_class_sub, class_subcategories.title_class_sub,
> class_ads.id_class_ad, class_ads.title_class_ad
> FROM ((class_categories INNER JOIN class_subcategories ON
> class_categories.id_class_cat = class_subcategories.id_class_cat) INNER
> JOIN ((CITIES INNER JOIN class_ads ON CITIES.id_cities =
> class_ads.id_cities) INNER JOIN ZIPCODES ON CITIES.id_cities =
> ZIPCODES.id_cities) ON class_subcategories.id_class_sub =
> class_ads.id_class_sub) INNER JOIN main1 ON ZIPCODES.id_zip =
main1.id_zip;
> ----------
>
> And I wanted to eliminate the un-necessary tables from this query
> (tables which i am not using to extract data like CITIES and ZIPCODES).
>
> Any other ideas based on this?
>
> Thanks.

Re: Problem with a SQL...

Mircea wrote:
> Mike Brind wrote:
> > Mircea wrote:
> >> Mike Brind wrote:
> >>> Mircea wrote:
> >>>> Hi,
> >>>>
> >>>> I have this SQL
> >>>> --------
> >>>> SELECT main1.id_main,
> >>>> main1.firstname,main1.lastname,class_ads.id_class_ad,
> >>>> class_ads.title_class_ad,class_ads.date_posted,
> >>>> class_subcategories.id_class_sub,class_subcategories.title_c lass_sub,class_categories.id_class_cat,class_categories.titl e_class_cat
> >>>> FROM ((class_categories INNER JOIN class_subcategories ON
> >>>> (class_categories.id_class_cat = class_subcategories.id_class_cat AND
> >>>> class_categories.id_class_cat=4)) INNER JOIN (main1 INNER JOIN class_ads
> >>>> ON ( main1.id_main = class_ads.id_main AND class_ads.id_cities = 89)))
> >>>> ORDER BY date_posted desc;
> >>>> -------
> >>>>
> >>>> But I still get this error when I am trying to run it in an ASP page
> >>>> (Access as database):
> >>>> --------
> >>>> Microsoft JET Database Engine (0x80040E14)
> >>>> Syntax error in FROM clause
> >>>> --------
> >>>>
> >>>> As you see I am not using any reserved words here...so from what could
> >>>> it be this error?
> >>>>
> >>>> What wrong with my SQL?
> >>>>
> >>>> Thanks.
> >>> The best way to put these kinds of queries together is to use the tool
> >>> provided by Access for doing so - the Query Builder. You can examine
> >>> the SQL that Access produces and match it against your freehand efforts
> >>> to see what differences (if any) there are between the two.
> >>>
> >>> If you had done so, you would have seen that Access puts the criteria
> >>> in a WHERE clause, not as part of the JOIN conditions:
> >>>
> >>> SELECT main1.id_main, main1.firstname, main1.lastname,
> >>> class_ads.id_class_ad, class_ads.title_class_ad, class_ads.date_posted,
> >>> class_subcategories.id_class_sub, class_subcategories.title_class_sub,
> >>> class_categories.id_class_cat, class_categories.title_class_cat FROM
> >>> ((class_categories INNER JOIN class_subcategories ON (
> >>> class_categories.id_class_cat = class_subcategories.id_class_cat))
> >>> INNER JOIN (main1 INNER JOIN class_ads ON (main1.id_main =
> >>> class_ads.id_main ))) WHERE class_categories.id_class_cat=4 AND
> >>> class_ads.id_cities=89 ORDER BY date_posted desc;
> >>>
> >>> --
> >>> Mike Brind
> >>>
> >> I get the same error...unfortunately.
> >> I wanted to use this SQL as model (it uses three tables...mine has four
> >> tables):
> >> ------------
> >> sql_comments="SELECT main1.id_main, main1.firstname,
> >> events_comments.id_comments,events_comments.body_comments,ev ents_comments.date_posted,events_comments.time_posted,
> >> images_people.filepath FROM ( main1 INNER JOIN events_comments ON (
> >> main1.id_main = events_comments.id_main AND events_comments.id_event =
> >> "& id_event &")) LEFT JOIN images_people ON (main1.id_main =
> >> images_people.id_main AND images_people.mainpic = 1) ORDER BY
> >> events_comments.date_posted desc,events_comments.time_posted desc"
> >> -------------
> >>
> >> The original query was this:
> >> ---------
> >> sql_cat_all="SELECT class_ads.id_class_ad, class_ads.title_class_ad,
> >> class_ads.posted_by, class_ads.id_main, class_ads.date_posted,
> >> class_subcategories.id_class_sub,class_subcategories.title_c lass_sub,class_categories.id_class_cat,class_categories.titl e_class_cat
> >> FROM (class_categories INNER JOIN class_subcategories ON
> >> class_categories.id_class_cat = class_subcategories.id_class_cat) INNER
> >> JOIN class_ads ON class_subcategories.id_class_sub =
> >> class_ads.id_class_sub WHERE class_categories.id_class_cat=4 AND
> >> class_ads.id_cities=89 ORDER BY date_posted desc"
> >> ---------
> >>
> >> Which was working fine...the problem was that I wanted to have
> >> "main1.firstname, main1.lastname" taken from the table "main1" (in the
> >> original SQL above the field "class_ads.posted_by" held the firstname
> >> and the lastname (and this is information redundancy).
> >>
> >> So, at the original SQL I wanted to add a fourth table which holds the
> >> firstname and lastname ("main1").
> >>
> >> Thanks.
> >
> > Have you tried using Access's Query Builder yet?
> >
> > --
> > Mike Brind
> >
>
> Yes, I used it...but it adds two un-necessary tables to the query, two
> more inner joins...
>
> The query looks like this:
> ------------
> SELECT main1.id_main, main1.firstname, maininfo.lastname,
> class_categories.id_class_cat, class_categories.title_class_cat,
> class_subcategories.id_class_sub, class_subcategories.title_class_sub,
> class_ads.id_class_ad, class_ads.title_class_ad
> FROM ((class_categories INNER JOIN class_subcategories ON
> class_categories.id_class_cat = class_subcategories.id_class_cat) INNER
> JOIN ((CITIES INNER JOIN class_ads ON CITIES.id_cities =
> class_ads.id_cities) INNER JOIN ZIPCODES ON CITIES.id_cities =
> ZIPCODES.id_cities) ON class_subcategories.id_class_sub =
> class_ads.id_class_sub) INNER JOIN main1 ON ZIPCODES.id_zip = main1.id_zip;
> ----------
>
> And I wanted to eliminate the un-necessary tables from this query
> (tables which i am not using to extract data like CITIES and ZIPCODES).
>
> Any other ideas based on this?
>
> Thanks.

Yes. When you are using the query builder, the first thing it shows
you is an Add Table dialogue box. You have the choice as to which
tables to add to your query. The only idea I can come up with is, at
the point that you choose which tables to add, try NOT adding the
unnecessary tables.

--
Mike Brind

Re: Problem with a SQL...

Mike Brind wrote:
> Mircea wrote:
> > Mike Brind wrote:
> > > Mircea wrote:
> > >> Mike Brind wrote:
> > >>> Mircea wrote:
> > >>>> Hi,
> > >>>>
> > >>>> I have this SQL
> > >>>> --------
> > >>>> SELECT main1.id_main,
> > >>>> main1.firstname,main1.lastname,class_ads.id_class_ad,
> > >>>> class_ads.title_class_ad,class_ads.date_posted,
> > >>>> class_subcategories.id_class_sub,class_subcategories.title_c lass_sub,class_categories.id_class_cat,class_categories.titl e_class_cat
> > >>>> FROM ((class_categories INNER JOIN class_subcategories ON
> > >>>> (class_categories.id_class_cat = class_subcategories.id_class_cat AND
> > >>>> class_categories.id_class_cat=4)) INNER JOIN (main1 INNER JOIN class_ads
> > >>>> ON ( main1.id_main = class_ads.id_main AND class_ads.id_cities = 89)))
> > >>>> ORDER BY date_posted desc;
> > >>>> -------
> > >>>>
> > >>>> But I still get this error when I am trying to run it in an ASP page
> > >>>> (Access as database):
> > >>>> --------
> > >>>> Microsoft JET Database Engine (0x80040E14)
> > >>>> Syntax error in FROM clause
> > >>>> --------
> > >>>>
> > >>>> As you see I am not using any reserved words here...so from what could
> > >>>> it be this error?
> > >>>>
> > >>>> What wrong with my SQL?
> > >>>>
> > >>>> Thanks.
> > >>> The best way to put these kinds of queries together is to use the tool
> > >>> provided by Access for doing so - the Query Builder. You can examine
> > >>> the SQL that Access produces and match it against your freehand efforts
> > >>> to see what differences (if any) there are between the two.
> > >>>
> > >>> If you had done so, you would have seen that Access puts the criteria
> > >>> in a WHERE clause, not as part of the JOIN conditions:
> > >>>
> > >>> SELECT main1.id_main, main1.firstname, main1.lastname,
> > >>> class_ads.id_class_ad, class_ads.title_class_ad, class_ads.date_posted,
> > >>> class_subcategories.id_class_sub, class_subcategories.title_class_sub,
> > >>> class_categories.id_class_cat, class_categories.title_class_cat FROM
> > >>> ((class_categories INNER JOIN class_subcategories ON (
> > >>> class_categories.id_class_cat = class_subcategories.id_class_cat))
> > >>> INNER JOIN (main1 INNER JOIN class_ads ON (main1.id_main =
> > >>> class_ads.id_main ))) WHERE class_categories.id_class_cat=4 AND
> > >>> class_ads.id_cities=89 ORDER BY date_posted desc;
> > >>>
> > >>> --
> > >>> Mike Brind
> > >>>
> > >> I get the same error...unfortunately.
> > >> I wanted to use this SQL as model (it uses three tables...mine has four
> > >> tables):
> > >> ------------
> > >> sql_comments="SELECT main1.id_main, main1.firstname,
> > >> events_comments.id_comments,events_comments.body_comments,ev ents_comments.date_posted,events_comments.time_posted,
> > >> images_people.filepath FROM ( main1 INNER JOIN events_comments ON (
> > >> main1.id_main = events_comments.id_main AND events_comments.id_event =
> > >> "& id_event &")) LEFT JOIN images_people ON (main1.id_main =
> > >> images_people.id_main AND images_people.mainpic = 1) ORDER BY
> > >> events_comments.date_posted desc,events_comments.time_posted desc"
> > >> -------------
> > >>
> > >> The original query was this:
> > >> ---------
> > >> sql_cat_all="SELECT class_ads.id_class_ad, class_ads.title_class_ad,
> > >> class_ads.posted_by, class_ads.id_main, class_ads.date_posted,
> > >> class_subcategories.id_class_sub,class_subcategories.title_c lass_sub,class_categories.id_class_cat,class_categories.titl e_class_cat
> > >> FROM (class_categories INNER JOIN class_subcategories ON
> > >> class_categories.id_class_cat = class_subcategories.id_class_cat) INNER
> > >> JOIN class_ads ON class_subcategories.id_class_sub =
> > >> class_ads.id_class_sub WHERE class_categories.id_class_cat=4 AND
> > >> class_ads.id_cities=89 ORDER BY date_posted desc"
> > >> ---------
> > >>
> > >> Which was working fine...the problem was that I wanted to have
> > >> "main1.firstname, main1.lastname" taken from the table "main1" (in the
> > >> original SQL above the field "class_ads.posted_by" held the firstname
> > >> and the lastname (and this is information redundancy).
> > >>
> > >> So, at the original SQL I wanted to add a fourth table which holds the
> > >> firstname and lastname ("main1").
> > >>
> > >> Thanks.
> > >
> > > Have you tried using Access's Query Builder yet?
> > >
> > > --
> > > Mike Brind
> > >
> >
> > Yes, I used it...but it adds two un-necessary tables to the query, two
> > more inner joins...
> >
> > The query looks like this:
> > ------------
> > SELECT main1.id_main, main1.firstname, maininfo.lastname,
> > class_categories.id_class_cat, class_categories.title_class_cat,
> > class_subcategories.id_class_sub, class_subcategories.title_class_sub,
> > class_ads.id_class_ad, class_ads.title_class_ad
> > FROM ((class_categories INNER JOIN class_subcategories ON
> > class_categories.id_class_cat = class_subcategories.id_class_cat) INNER
> > JOIN ((CITIES INNER JOIN class_ads ON CITIES.id_cities =
> > class_ads.id_cities) INNER JOIN ZIPCODES ON CITIES.id_cities =
> > ZIPCODES.id_cities) ON class_subcategories.id_class_sub =
> > class_ads.id_class_sub) INNER JOIN main1 ON ZIPCODES.id_zip = main1.id_zip;
> > ----------
> >
> > And I wanted to eliminate the un-necessary tables from this query
> > (tables which i am not using to extract data like CITIES and ZIPCODES).
> >
> > Any other ideas based on this?
> >
> > Thanks.
>
> Yes. When you are using the query builder, the first thing it shows
> you is an Add Table dialogue box. You have the choice as to which
> tables to add to your query. The only idea I can come up with is, at
> the point that you choose which tables to add, try NOT adding the
> unnecessary tables.
>

Having said that, it seems that Cities and Zipcodes are intrinsic to
the relationship in the query. You have no relationship between Main1
and the other 3 tables you are drawing data from except through Cities
and Zipcodes. In that case, the joins through those two tables are a
necessary part of the query, and should be left in.

Incidentally, is maininfo a table in your DB? Only you seem to be
selecting the lastname from maininfo, and the firstname from main1. If
maininfo is a typo, here's another suggestion: Ctrl + C, Ctrl + V.

--
Mike Brind

Re: Problem with a SQL...

Mike Brind wrote:
> Mircea wrote:
>> Mike Brind wrote:
>>> Mircea wrote:
>>>> Mike Brind wrote:
>>>>> Mircea wrote:
>>>>>> Hi,
>>>>>>
>>>>>> I have this SQL
>>>>>> --------
>>>>>> SELECT main1.id_main,
>>>>>> main1.firstname,main1.lastname,class_ads.id_class_ad,
>>>>>> class_ads.title_class_ad,class_ads.date_posted,
>>>>>> class_subcategories.id_class_sub,class_subcategories.title_c lass_sub,class_categories.id_class_cat,class_categories.titl e_class_cat
>>>>>> FROM ((class_categories INNER JOIN class_subcategories ON
>>>>>> (class_categories.id_class_cat = class_subcategories.id_class_cat AND
>>>>>> class_categories.id_class_cat=4)) INNER JOIN (main1 INNER JOIN class_ads
>>>>>> ON ( main1.id_main = class_ads.id_main AND class_ads.id_cities = 89)))
>>>>>> ORDER BY date_posted desc;
>>>>>> -------
>>>>>>
>>>>>> But I still get this error when I am trying to run it in an ASP page
>>>>>> (Access as database):
>>>>>> --------
>>>>>> Microsoft JET Database Engine (0x80040E14)
>>>>>> Syntax error in FROM clause
>>>>>> --------
>>>>>>
>>>>>> As you see I am not using any reserved words here...so from what could
>>>>>> it be this error?
>>>>>>
>>>>>> What wrong with my SQL?
>>>>>>
>>>>>> Thanks.
>>>>> The best way to put these kinds of queries together is to use the tool
>>>>> provided by Access for doing so - the Query Builder. You can examine
>>>>> the SQL that Access produces and match it against your freehand efforts
>>>>> to see what differences (if any) there are between the two.
>>>>>
>>>>> If you had done so, you would have seen that Access puts the criteria
>>>>> in a WHERE clause, not as part of the JOIN conditions:
>>>>>
>>>>> SELECT main1.id_main, main1.firstname, main1.lastname,
>>>>> class_ads.id_class_ad, class_ads.title_class_ad, class_ads.date_posted,
>>>>> class_subcategories.id_class_sub, class_subcategories.title_class_sub,
>>>>> class_categories.id_class_cat, class_categories.title_class_cat FROM
>>>>> ((class_categories INNER JOIN class_subcategories ON (
>>>>> class_categories.id_class_cat = class_subcategories.id_class_cat))
>>>>> INNER JOIN (main1 INNER JOIN class_ads ON (main1.id_main =
>>>>> class_ads.id_main ))) WHERE class_categories.id_class_cat=4 AND
>>>>> class_ads.id_cities=89 ORDER BY date_posted desc;
>>>>>
>>>>> --
>>>>> Mike Brind
>>>>>
>>>> I get the same error...unfortunately.
>>>> I wanted to use this SQL as model (it uses three tables...mine has four
>>>> tables):
>>>> ------------
>>>> sql_comments="SELECT main1.id_main, main1.firstname,
>>>> events_comments.id_comments,events_comments.body_comments,ev ents_comments.date_posted,events_comments.time_posted,
>>>> images_people.filepath FROM ( main1 INNER JOIN events_comments ON (
>>>> main1.id_main = events_comments.id_main AND events_comments.id_event =
>>>> "& id_event &")) LEFT JOIN images_people ON (main1.id_main =
>>>> images_people.id_main AND images_people.mainpic = 1) ORDER BY
>>>> events_comments.date_posted desc,events_comments.time_posted desc"
>>>> -------------
>>>>
>>>> The original query was this:
>>>> ---------
>>>> sql_cat_all="SELECT class_ads.id_class_ad, class_ads.title_class_ad,
>>>> class_ads.posted_by, class_ads.id_main, class_ads.date_posted,
>>>> class_subcategories.id_class_sub,class_subcategories.title_c lass_sub,class_categories.id_class_cat,class_categories.titl e_class_cat
>>>> FROM (class_categories INNER JOIN class_subcategories ON
>>>> class_categories.id_class_cat = class_subcategories.id_class_cat) INNER
>>>> JOIN class_ads ON class_subcategories.id_class_sub =
>>>> class_ads.id_class_sub WHERE class_categories.id_class_cat=4 AND
>>>> class_ads.id_cities=89 ORDER BY date_posted desc"
>>>> ---------
>>>>
>>>> Which was working fine...the problem was that I wanted to have
>>>> "main1.firstname, main1.lastname" taken from the table "main1" (in the
>>>> original SQL above the field "class_ads.posted_by" held the firstname
>>>> and the lastname (and this is information redundancy).
>>>>
>>>> So, at the original SQL I wanted to add a fourth table which holds the
>>>> firstname and lastname ("main1").
>>>>
>>>> Thanks.
>>> Have you tried using Access's Query Builder yet?
>>>
>>> --
>>> Mike Brind
>>>
>> Yes, I used it...but it adds two un-necessary tables to the query, two
>> more inner joins...
>>
>> The query looks like this:
>> ------------
>> SELECT main1.id_main, main1.firstname, maininfo.lastname,
>> class_categories.id_class_cat, class_categories.title_class_cat,
>> class_subcategories.id_class_sub, class_subcategories.title_class_sub,
>> class_ads.id_class_ad, class_ads.title_class_ad
>> FROM ((class_categories INNER JOIN class_subcategories ON
>> class_categories.id_class_cat = class_subcategories.id_class_cat) INNER
>> JOIN ((CITIES INNER JOIN class_ads ON CITIES.id_cities =
>> class_ads.id_cities) INNER JOIN ZIPCODES ON CITIES.id_cities =
>> ZIPCODES.id_cities) ON class_subcategories.id_class_sub =
>> class_ads.id_class_sub) INNER JOIN main1 ON ZIPCODES.id_zip = main1.id_zip;
>> ----------
>>
>> And I wanted to eliminate the un-necessary tables from this query
>> (tables which i am not using to extract data like CITIES and ZIPCODES).
>>
>> Any other ideas based on this?
>>
>> Thanks.
>
> Yes. When you are using the query builder, the first thing it shows
> you is an Add Table dialogue box. You have the choice as to which
> tables to add to your query. The only idea I can come up with is, at
> the point that you choose which tables to add, try NOT adding the
> unnecessary tables.
>
> --
> Mike Brind
>

I know about that... But those tables are added AUTOMATICALLY by the
query builder! Because all tables are in some relationship one to
anonther (I am selecting only the tables I need but the query adds those
two tables too).

-------
Having said that, it seems that Cities and Zipcodes are intrinsic to
the relationship in the query. You have no relationship between Main1
and the other 3 tables you are drawing data from except through Cities
and Zipcodes. In that case, the joins through those two tables are a
necessary part of the query, and should be left in.
-------

Yes, you're right...but...in this query example the situation is the same!:
----------
sql_comments="SELECT main1.id_main, main1.firstname,
events_comments.id_comments,events_comments.body_comments,ev ents_comments.date_posted,events_comments.time_posted,
images_people.filepath FROM ( main1 INNER JOIN events_comments ON (
main1.id_main = events_comments.id_main AND events_comments.id_event =
"& id_event &")) LEFT JOIN images_people ON (main1.id_main =
images_people.id_main AND images_people.mainpic = 1) ORDER BY
events_comments.date_posted desc,events_comments.time_posted desc"
---------

When I built the original query in Query Builder I got the same Cities
and Zipcodes table in my query...but somebody else helped me and the
query above is the result...and it's working fine.

Now, that somebody else cannot help me anymore(lost the contact)...

--------
Incidentally, is maininfo a table in your DB? Only you seem to be
selecting the lastname from maininfo, and the firstname from main1. If
maininfo is a typo, here's another suggestion: Ctrl + C, Ctrl + V.
--------

Yes, "maininfo" was a typo...it should be "main1" instead...

Re: Problem with a SQL...

Mircea wrote:



> Having said that, it seems that Cities and Zipcodes are intrinsic to
> the relationship in the query. You have no relationship between Main1
> and the other 3 tables you are drawing data from except through Cities
> and Zipcodes. In that case, the joins through those two tables are a
> necessary part of the query, and should be left in.
> -------
>
> Yes, you're right...but...in this query example the situation is the same!:
> ----------
> sql_comments="SELECT main1.id_main, main1.firstname,
> events_comments.id_comments,events_comments.body_comments,ev ents_comments.date_posted,events_comments.time_posted,
> images_people.filepath FROM ( main1 INNER JOIN events_comments ON (
> main1.id_main = events_comments.id_main AND events_comments.id_event =
> "& id_event &")) LEFT JOIN images_people ON (main1.id_main =
> images_people.id_main AND images_people.mainpic = 1) ORDER BY
> events_comments.date_posted desc,events_comments.time_posted desc"
> ---------
>
> When I built the original query in Query Builder I got the same Cities
> and Zipcodes table in my query...but somebody else helped me and the
> query above is the result...and it's working fine.
>

Clearly the example above is from different tables that don't rely on
foreign keys in the Zipcodes and Cities tables. You should look at the
Access Help and read up about relationships, Primary and Foreign Keys
and Joins in queries.

--
Mike Brind