SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Addict Feudalism's Avatar
    Join Date
    Jun 2002
    Location
    Buenos Aires, Argentina
    Posts
    361
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Where should I define a table alias if im calling it in a outer join?

    Code:
    SELECT clientes_alias. * 
    , SUM( CASE  WHEN cotizaciones_alias.estado > 2.00 THEN items_de_cotizaciones_alias.costo_total ELSE 0 END  )  AS ventas_a_cliente
    FROM clientes clientes_alias
    LEFT  OUTER  JOIN clientes ON cotizaciones_alias.cliente = clientes_alias.id
    LEFT  OUTER  JOIN cotizaciones cotizaciones_alias ON items_de_cotizaciones_alias.cotizacion = cotizaciones_alias.id
    GROUP  BY clientes_alias.id
    SQL SAYS:

    Code:
    #1109 - Unknown table 'items_de_cotizaciones_alias' in field list

  2. #2
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,578
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    There's no table aliased items_de_cotizaciones_alias anywhere in this query... where is it supposed to find those columns?

    Any reason you're giving extremely long aliases to unique table names? They're meant to make the query easier to read, and resolve ambiguities when a table is joined more than once.

  3. #3
    SitePoint Addict Feudalism's Avatar
    Join Date
    Jun 2002
    Location
    Buenos Aires, Argentina
    Posts
    361
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question

    Thank you for your answer.

    The reason Im using sooooo looooong table names and aliases (all aliases are equal, it's the table's name plus '_alias') is because all those tables are dynamically generated, and so are it's names, and the generator is a really huge CMS, now having, for example, 245 different tables, and most of names are similar.

    I know im not defining the table, but I dont know where should I define it. Just in case, I have tried not using the alias in this query, and it works perfect. ( instead using items_de_cotizaciones_alias, i used items_de_cotizaciones ) The problem is... I dont' know where should I define the alias name.

  4. #4
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,578
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    After the table name. Problem is, items_de_cotizaciones is not one of the tables listed in the FROM or the JOIN list of this query...


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •