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

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
[email protected]

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

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
[email protected]

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

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
[email protected]

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