Outer Join Problem

[markbanker]

I'm having a problem with this query and I can't see the error. All of the tables and fields referenced do exist in the DB. The error is listed below. Here's the query:

<CFIF IsDefined(&quot;form.category&quot>
<CFQUERY NAME=&quot;InventoryRef&quot; DATASOURCE=&quot;emscodb&quot;>
SELECT inventory.*, mk_desc, ca_desc FROM inventory
LEFT OUTER JOIN make ON in_mk_id = mk_id
LEFT OUTER JOIN category ON in_ca_id = ca_id
WHERE in_ca_id = #Form.category#
</CFQUERY>
</CFIF>

ODBC Error Code = 37000 (Syntax error or access violation)


[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'in_mk_id = mk_id LEFT OUTER JOIN category ON in_ca_id = ca_id'.

Thanks

 

[Silvano]

you have to specify the table name of the every field you are getting data from;
example: I don't know from which table is 'mk_desc' or 'ca_desc' field? try including table name:
SELECT inventory.*, make.mk_desc, make.ca_desc (if 'make' is the name of the second table');
is the 'category' third table? try adding 'AND' between those two 'LEFT OUTER JOIN';
specify from which table you are getting 'in_ca_id' field...
those are just few tips, hope it helps you...

<CFIF IsDefined(&quot;form.category&quot>
<CFQUERY NAME=&quot;InventoryRef&quot; DATASOURCE=&quot;emscodb&quot;>
SELECT inventory.*, tableName.mk_desc, tableName.ca_desc
FROM inventory
LEFT OUTER JOIN make ON tableName.in_mk_id = tableName.mk_id AND
LEFT OUTER JOIN category ON tableName.in_ca_id = tableName.ca_id
WHERE tableName.in_ca_id = #Form.category# #Form.category#
</CFQUERY>
</CFIF>


those are just few tips, hope it helps you...
Sylvano
dsylvano@hotmail.com

&quot;every and each day when I learn something new is a small victory...&quot;

 

[markbanker]

I tried all of these ideas, and the error remained the same. Any other ideas?

Thanks,

Mark

 

[Silvano]

you have to be little more specific when working with SQL; for example: in WHERE clause you are asking for tableName.in_ca_id = #Form.category#, but in the SELECT part you are not getting that field(in_ca_id) from any of the tables; is that field part of the inventory table where you are using whild card?;
second, simplify the whole query and try to narrow it down the exact origin of the error; throw out the second LEFT OUTER JOIN and try make it work with only two tables; if it works, then we will make it more complicated and add third table, ok?
Sylvano
dsylvano@hotmail.com

&quot;every and each day when I learn something new is a small victory...&quot;