LEFT JOIN & PARENTHESIS

alagoro

When using JOIN on select there are no parenthesis so SQL gives an error.

This says error:
SELECT A.field1, B.field2
FROM table1 A
LEFT OUTER JOIN table2 B ON A.field1=B.field1
LEFT OUTER JOIN table3 C ON A.field3=C.field1
WHERE A.field1 = '1234'

This is how I manually correct it:
SELECT A.field1, B.field2
FROM (table1 A
LEFT OUTER JOIN table2 B ON A.field1=B.field1)
LEFT OUTER JOIN table3 C ON A.field3=C.field1
WHERE A.field1 = '1234'

From here on any change to SQL must be made manually. Anybody has same problem?

Junior Puttinati

I use this:

SELECT A.field1, B.field2
FROM table1 A
LEFT OUTER JOIN table2 B ON (A.field1=B.field1)
LEFT OUTER JOIN table3 C ON (A.field3=C.field1)
WHERE A.field1 = '1234'

Works perfect ..

alagoro

That code doesn't work to me. I'm using MS Access.

The next code comes out of the Fast Query Builder Designer.

SELECT t.CODIGO_RIESGO, t.CODIGO_MAQ, t.CODIGO_REQ, C.CODIGO_CONTACTO, C.FECHA_CONTACTO, C.CODIGO_EMPRESA, C.CODIGO_TIPOCONTACTO, C.NOTAS_CONTACTO, P.*
FROM
    Presupuestos_contratos P
    LEFT OUTER JOIN Contactos C ON (P.CODIGO_PC=C.CODIGO_PC)
    LEFT OUTER JOIN tblRiesgos t ON (P.CODIGO_RIESGO=t.CODIGO_RIESGO)

(I can't paste imgages if they anr not on a public http site ;'))

technisoft

All the quesries, including the code sample, should work without requiring any brackets. At least it does in Firebird which, unlike MS Access is one of the most SQL standard compliant databases around. MS products are notorious for not being standard compliant.

The only problem I can see in the original query is the reference to table3 whereas no table3 field is extracted.
I suspect it is an MS Access problem, does the query work properly within MS Access?

alagoro

The problem is Fast report Query builder creates a query that when you try to execute in the same builder it fails.

The first example was created by me here, but on my second message I created the code directly from the query builder.

The error window says "nexo" is not found (sorry I don't know how to translate the word 'nexo') and gives 2 lines of the code.

The query will be ok if parenthesis are added rounding the joins, but after this the designer says there have been changes so it won't design automatically any more.

btw the query does not work in Access. The error says (in spanish):

"Error de sintaxis (falta operador) en la expresion de consulta '(P.CODIGO_PC=C.CODIGO_PC) LEFT OUTER JOIN tblRiesgos t ON (P.CODIGO_RIESGO=t.CODIGO_RIESGO)"

The query goes fine this way:

FROM
(Presupuestos_contratos P
LEFT OUTER JOIN Contactos C ON (P.CODIGO_PC=C.CODIGO_PC))
LEFT OUTER JOIN tblRiesgos t ON (P.CODIGO_RIESGO=t.CODIGO_RIESGO)

thanks,
Alberto.