LEFT JOIN & PARENTHESIS

alagoroalagoro
edited 3:00PM in FastReport 4.0
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?

Comments

  • Junior PuttinatiJunior Puttinati
    edited 3:00PM
    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 ..
  • alagoroalagoro
    edited 3:00PM
    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 ;'))

  • technisofttechnisoft
    edited 3:00PM
    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?
  • alagoroalagoro
    edited July 2010
    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.

Leave a Comment

Rich Text Editor. To edit a paragraph's style, hit tab to get to the paragraph menu. From there you will be able to pick one style. Nothing defaults to paragraph. An inline formatting menu will show up when you select text. Hit tab to get into that menu. Some elements, such as rich link embeds, images, loading indicators, and error messages may get inserted into the editor. You may navigate to these using the arrow keys inside of the editor and delete them with the delete or backspace key.

Home • FastReport 4.0Comment As ...

© Vanilla Keystone Theme 2024