Probable compatibility issue in query

[wiltonrossi]

Hello people,

I'm in deep trouble due to a probable compatibility issue. I and a friend of mine developed a solution with the help of Microsoft Access, which was used with the single purpose of creating the following query:

------

SELECT DISTINCT s_municipio.sg_uf, s_municipio.co_municipio, s_agente_epp.st_artesao,

s_agente_epp.id_orgao, s_municipio.no_municipio

FROM s_municipio INNER JOIN ((s_empresa INNER JOIN s_setor_produtivo ON s_empresa.co_setor =

s_setor_produtivo.co_setor) INNER JOIN s_agente_epp ON s_empresa.id_empresa =

s_agente_epp.id_empresa) ON s_municipio.co_municipio = s_empresa.nm_cidade_empresa

GROUP BY s_municipio.sg_uf, s_municipio.co_municipio, s_agente_epp.st_artesao,

s_agente_epp.id_orgao, s_municipio.no_municipio

HAVING (((s_municipio.sg_uf)='"& UF &"') AND ((s_agente_epp.st_artesao) Is Null) AND

((s_agente_epp.id_orgao) Is Null))

ORDER BY s_municipio.no_municipio

------

(Please note that tables and fields names are in Portuguese and also the presence of an ASP variable called UF...)

We made as many tests as we could and it worked fine on MySQL version 4.0.

But when I tried to use it on a server with version 3.23 installed - the server on which it's supposed to run - I received the following error:

------

Microsoft OLE DB Provider for ODBC Drivers error '80040e09'

[TCX][MyODBC]You have an error in your SQL syntax near '(s_agente_epp INNER JOIN s_orgao ON s_agente_epp.id_orgao = s_orgao.id_orgao) ON' at line 1

/area_rest/RecuperacaoEPP/Form_recuperacao.asp, line 81

------

I talked to friends and they think it is probably a problem caused by the use of JOIN, which doesn't work properly on 3.23.

I was told to rewrite it, but, after several attempts and failures, I gave up. Maybe someone can tell me what is wrong and how can I make it work. It would be greatly appreciated!!!

Regards,

Wilton Rossi

 

[sleipnir214]

Here's one confusion I'm having...

MySQL reports that the error is near the string "(s_agente_epp INNER JOIN s_orgao ON s_agente_epp.id_orgao = s_orgao.id_orgao) ON".

However, that string appears no where in the SQL query you posted. Are you sure you're looking at the right query?

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!!

 

[wiltonrossi]

How stupid! I'm sorry. I pasted the wrong portion of the code into the message.

The query presenting the syntax error is the following:

------

SELECT s_municipio.co_municipio, s_municipio.no_municipio, s_municipio.sg_uf, s_agente_epp.st_artesao, s_agente_epp.id_empresa

FROM s_municipio INNER JOIN (s_agente_epp INNER JOIN s_orgao ON s_agente_epp.id_orgao = s_orgao.id_orgao) ON s_municipio.co_municipio = s_orgao.nm_cidade_orgao

GROUP BY s_municipio.co_municipio, s_municipio.no_municipio, s_municipio.sg_uf, s_agente_epp.st_artesao, s_agente_epp.id_empresa

HAVING (((s_municipio.co_municipio) Is Not Null And (s_municipio.co_municipio)<>'') AND ((s_municipio.no_municipio) Is Not Null And (s_municipio.no_municipio)<>'') AND ((s_municipio.sg_uf)='&quot;& UF &&quot;') AND ((s_agente_epp.st_artesao) Is Null) AND ((s_agente_epp.id_empresa) Is Null))

ORDER BY s_municipio.co_municipio&quot;

 

[sleipnir214]

The problem is your nesting of joins through the use of a subquery. MySQL does allow the chaining of joins, but the syntax has to be something like:

FROM
TableA
JOIN
TableB
ON
Condition1
JOIN
TableC
ON
Condition2


Also, this page (

http://www.mysql.com/doc/en/JOIN.html)

from the MySQL online documentation states that &quot;FROM A INNER JOIN B ON C&quot; is equivalent to &quot;FROM A, B WHERE C&quot;, so it shouldn't be too hard to rewrite the query. Something of the form:

FROM
TableA
JOIN
TableB
ON
Condition1,
TableC
JOIN
TableD
ON
Condition2
WHERE
Condition3 <= which establishes the relationship between the two joins





Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!!

 

[swampBoogie]

Just remove the parentheses

SELECT DISTINCT s_municipio.sg_uf, s_municipio.co_municipio, s_agente_epp.st_artesao,
s_agente_epp.id_orgao, s_municipio.no_municipio
FROM s_municipio INNER JOIN s_empresa INNER JOIN s_setor_produtivo ON s_empresa.co_setor =
s_setor_produtivo.co_setor INNER JOIN s_agente_epp ON s_empresa.id_empresa =
s_agente_epp.id_empresa ON s_municipio.co_municipio = s_empresa.nm_cidade_empresa

where s_municipio.sg_uf ='"& UF &"'
AND s_agente_epp.st_artesao Is Null
AND s_agente_epp.id_orgao Is Null

GROUP BY s_municipio.sg_uf, s_municipio.co_municipio, s_agente_epp.st_artesao,
s_agente_epp.id_orgao, s_municipio.no_municipio

ORDER BY s_municipio.no_municipio

I also moved the conditions in the having clause to the where clause as there is no need for having in this case. Using having in vain may cause a performance hit.