Problem with a SQL... - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Problem with a SQL...

Author

Topic

mirceagoia
Starting Member

6 Posts

Posted - 2006-05-05 : 02:43:37

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_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 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.

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2006-05-05 : 03:38:34

start from your "from" clause

from should be followed by a datasource like a table or similar object...

parentheses not needed unless that is not your intention


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 
AND class_categories.id_class_cat=4 
INNER JOIN main1 
on ????
INNER JOIN class_ads 
ON main1.id_main = class_ads.id_main AND class_ads.id_cities = 89 
ORDER BY date_posted desc

--------------------
keeping it simple...

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-05-05 : 04:22:33

Jen, Access wont support AND class_categories.id_class_cat=4 and that should be Where class_categories.id_class_cat=4

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 
AND 
INNER JOIN main1 
on ????
INNER JOIN class_ads 
ON main1.id_main = class_ads.id_main AND class_ads.id_cities = 89 
Where class_categories.id_class_cat=4 
ORDER BY date_posted desc

Madhivanan

Failing to plan is Planning to fail

mirceagoia
Starting Member

6 Posts

Posted - 2006-05-05 : 04:29:24

Well, I have four tables: class_categories,class_subcategories,main1 and class_ads.

How do I join them? That's the question...

There's not other on "INNER JOIN main1 on ????"...

I tried to use this model:
---------
sql_comments="SELECT main1.id_main, main1.firstname, events_comments.id_comments,events_comments.body_comments,events_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 above query uses thre tables, not four...so i tried to adapt at four tables but seems I didn't succeded.

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2006-05-05 : 04:33:56

ok...hope you read the responses...

remove the parentheses, the from clause is expecting a table or similar object

is this access of mssql? your error says it's access but your query implies you're going to execute it as dsql

--------------------
keeping it simple...

mirceagoia
Starting Member

6 Posts

Posted - 2006-05-05 : 04:52:15

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_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 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").

mirceagoia
Starting Member

6 Posts

Posted - 2006-05-05 : 04:54:27

It is Access as backend...but I am using it in an ASP page...yes, I read the response and I removed the parenthesis...

But what should I put at "INNER JOIN main1 on ????" ?

KenW
Constraint Violating Yak Guru

391 Posts

Posted - 2006-05-05 : 10:24:17

mirceagoia,

"But what should I put at "INNER JOIN main1 on ????" ?"

Whatever connects main1 to the other table. Possibly "main1.id_main = events_comments.id_main"?

Ken

mirceagoia
Starting Member

6 Posts

Posted - 2006-05-05 : 14:25:36

I am not using "events_comments" table for anything in my query which I wanted to work.

That SQL with "events_comments" was an example...

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-05-05 : 14:40:11

mirceagoia,

U may have a reason to join the table Main1 ? If u do not want that just remove it with inner join ...
if u want to join that table, (or any table), u should find a related fied of that table to some other table (or a derived table)
U have to join using "those Fields" relating Main1 to any of the other tables u used.

Srinika

mirceagoia
Starting Member

6 Posts

Posted - 2006-05-05 : 18:08:56

Yes I do have a reason for joining "main1" table: from there I get the firstname and lastname for a record in "class_ads" table (in "class_ads" table I am keeping only the "id_main" of each record from "main1").

When I build this query in Query Builder form Access it gives me this:
-----------
SELECT main1.id_main, main1.firstname, main1.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;
-------

But as you see, I am not getting anything from Cities and Zipcodes tables. And i do have in "class_ads" the id of the cities...

I wanted to eliminate those two tables I am not using it: Citites and Zipcodes.

Because in this situation it was the same:
--------
sql_comments="SELECT main1.id_main, main1.firstname, events_comments.id_comments,events_comments.body_comments,events_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 it with query Builder I got the same Cities and Zipcodes table in this query...but somebody helped me and the result was this query which works fine (I lost the contact with that somebody).

So, I thought I may replicate the succes with my query where I have four tables involved (not three like the above)...but I wasn't successfull so far...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources