Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > SQL Server > SQL Server 2000 > SQL Server 2000
Password Reminder
Register
| FAQ | Members List | Search | Today's Posts | Mark Forums Read
SQL Server 2000 General discussion of Microsoft SQL Server -- for topics that don't fit in one of the more specific SQL Server forums. version 2000 only. There's a new forum for SQL Server 2005.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2000 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old December 2nd, 2007, 04:29 PM
Registered User
 
Join Date: Nov 2007
Location: , , Canada.
Posts: 9
Thanks: 0
Thanked 0 Times in 0 Posts
Default sql query question

I am trying to join three tables and I keep getting a syntax error:

syntax error in query expression r.intRegID = i.intRegID JOIN tbl_Categories c on i.intCategoryID=c.intCategoryID


The full query is:

SELECT *
from tbl_RegistrationInfo r INNER JOIN tbl_Ideas i
on r.intRegID = i.intRegID JOIN tbl_Categories c
on i.intCategoryID=c.intCategoryID
where i.intIdeaID=4
ORDER BY i.DateCreated

THe common key for the tables tbl_RegistrationInfo and tbl_Ideas is intRegID.
THe common key for the tables tbl_Ideas and tbl_Categories is intCategoryID.
And finally the user provides a value for the "idea" (i.intIdeaID=4).

I can't seem to find the error and it is driving me nuts. Can someone please help?

Thanks,
Luis



Reply With Quote
  #2 (permalink)  
Old December 2nd, 2007, 08:59 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: Hudson, MA, USA.
Posts: 839
Thanks: 0
Thanked 1 Time in 1 Post
Default

That second JOIN in your FROM clause, which kind is it?

A FROM clause needs to specify what <join type> the tables are being, er, JOINed on...

Jeff Mason
je.mason@comcast.net
Reply With Quote
  #3 (permalink)  
Old December 3rd, 2007, 02:09 PM
Registered User
 
Join Date: Nov 2007
Location: , , Canada.
Posts: 9
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I tried inner join and got an error. I then changed it to a plain join and get a 'from clause' error.

Here is a screenshot of my db:

http://www.heidisplayhouse.com/tables.gif

I have tried so many things and cant get the query to run.

Luis



Reply With Quote
  #4 (permalink)  
Old December 3rd, 2007, 02:28 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: Hudson, MA, USA.
Posts: 839
Thanks: 0
Thanked 1 Time in 1 Post
Default

There's no such thing as a 'plain join'.

You said you tried the inner join and got an error. What error did you get, and what was the query?

This ought to work:

SELECT *
from tbl_RegistrationInfo r INNER JOIN tbl_Ideas i
on r.intRegID = i.intRegID INNER JOIN tbl_Categories c
on i.intCategoryID=c.intCategoryID
where i.intIdeaID=4
ORDER BY i.DateCreated

...although I see you are using an Access database and this is a SQL Server forum. I'm not very good at Access (nor do I want to be :D ), so I'm just guessing here, but Access may want you to parenthesize the FROM clause (something like):

SELECT *
from ((tbl_RegistrationInfo r INNER JOIN tbl_Ideas i
on r.intRegID = i.intRegID) INNER JOIN tbl_Categories c
on i.intCategoryID=c.intCategoryID)
where i.intIdeaID=4
ORDER BY i.DateCreated

Have you tried using the Access Query Wizard to build up a query to give you an idea of the syntax?

Jeff Mason
je.mason@comcast.net
Reply With Quote
  #5 (permalink)  
Old December 3rd, 2007, 02:48 PM
Registered User
 
Join Date: Nov 2007
Location: , , Canada.
Posts: 9
Thanks: 0
Thanked 0 Times in 0 Posts
Default

SELECT *
from tbl_RegistrationInfo r INNER JOIN tbl_Ideas i
on r.intRegID = i.intRegID INNER JOIN tbl_Categories c
on i.intCategoryID=c.intCategoryID
where i.intIdeaID=4
ORDER BY i.DateCreated

Error: syntax error in query expression r.intRegID = i.intRegID JOIN tbl_Categories c on i.intCategoryID=c.intCategoryID

SELECT *
from ((tbl_RegistrationInfo r INNER JOIN tbl_Ideas i
on r.intRegID = i.intRegID) INNER JOIN tbl_Categories c
on i.intCategoryID=c.intCategoryID)
where i.intIdeaID=4
ORDER BY i.DateCreated

error: type mismatch in expression

I am going to launch my laptop out the window...

Luis

Reply With Quote
  #6 (permalink)  
Old December 3rd, 2007, 03:31 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: Hudson, MA, USA.
Posts: 839
Thanks: 0
Thanked 1 Time in 1 Post
Default

Don't throw out your laptop. Get rid of Access.

I think I see the problem. Your database table definitions have the int_RegID in the tbl_RegistrationInfo table defined as an autonumber. But the column you are attempting to JOIN on in the tbl_Ideas table has it defined as text.

The datatypes need to be consistent with one another.

Either change the table definition, or change the JOIN expression's ON clause to convert i.intRegID to a number - something like:

   ON r.intRegID = cint(i.intRegID)

(Don't do that. Change the column definition.)

(Note that you have defined some columns that look like they are dates to be text. Chances are you won't be happy with that, as your dates probably won't sort the way you'd like).

Jeff Mason
je.mason@comcast.net
Reply With Quote
  #7 (permalink)  
Old December 3rd, 2007, 03:41 PM
Registered User
 
Join Date: Nov 2007
Location: , , Canada.
Posts: 9
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Jeff,

YOU RULE!

your bracketed example worked like a charm when I made the datatype change.

Not my db and access seems to have its own issues.

I had a boss named Jeff Mason here in Toronto. Definitely wasn't as nice or helpful as you.

Thank you so much and have a good holiday!
Luis

Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Query Question Otacustes VB Databases Basics 6 July 15th, 2008 01:24 PM
Output Query to txt file from SQL Query everest SQL Server 2005 4 November 22nd, 2007 01:49 AM
SQL query question in Access dlamarche Access 3 April 3rd, 2005 10:42 PM
SQL Query question darkhalf Classic ASP Databases 4 March 17th, 2004 07:44 PM
Sql query question. pankaj_daga SQL Language 3 November 30th, 2003 03:04 PM



All times are GMT -4. The time now is 05:48 PM.


Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.