LEFT JOIN & PARENTHESIS

Comments

• Junior Puttinati

  July 2010 edited 9:10AM

  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

  July 2010 edited 9:10AM

  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

  July 2010 edited 9:10AM

  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

  July 2010 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.