inner join query is not working

[dunskii]

Hi there

I'm trying to create a query that looks at 3 table, where 1 table links the other 2.

My query is using 2 inner joins, i'm not sure if i can do this.

Well heres the query

SELECT com_categories.com_cat_id, com_categories.name, com_categories.description, com_categories.image, com_categories.directory, com_category_site.site_id, com_category_site.com_cat_id, site_details.site_id, site_details.name FROM com_categories INNER JOIN com_category_site INNER JOIN site_details ON com_categories.com_cat_id = com_category_site.com_cat_id AND com_category_site.site_id = site_details.site_id WHERE com_categories.com_cat_id EQ #id# ORDER BY site_details.name

And heres the error:

Syntax error in FROM clause.



The error occurred while processing an element with a general identifier of (CFQUERY)


thanks in advance

dunskii

 

[Sarky78]

Your problem appears to be with the first join. you have

FROM com_categories INNER JOIN com_category_site INNER JOIN

you need to specify which fields to join the table com_categories and com_category_site on

so this line should read:

FROM com_categories INNER JOIN com_category_site ON com_categories.JoinFieldName=com_category_site.JoinFieldName

can i suggest using aliases for the table names as well. this will make the code alot more readable

like this

SELECT a.whatever, b.whatever
FROM com_categories a INNER JOIN com_category_site b ON a.ID=b.ID

hope this helps !

 

[CFDude]

I think the problem is here:

WHERE com_categories.com_cat_id EQ #id#

Try:

WHERE com_categories.com_cat_id = #id#
(If ID is a string, enclose it in quotes -- '#id#')

You might want to try this:

SELECT com_categories.com_cat_id, com_categories.name, com_categories.description, com_categories.image, com_categories.directory, com_category_site.site_id, com_category_site.com_cat_id, site_details.site_id, site_details.name

From
com_categories INNER JOIN
(com_category_site INNER JOIN site_details on com_category_site.site_id = site_details.site_id)
ON com_categories.com_cat_id = com_category_site.com_cat_id

WHERE com_categories.com_cat_id = #id#

 

[dunskii]

ok, now i'm getting an error saying: Type mismatch in expression

 

[webmigit]

Take out there where statement... If your query works then, place it back in and if id looks like #id#, change it to '#id#'... if it looks like '#id#' change it to #id#..

Tony