use Perl

• Not moronic at all (Score:1)

  by Aristotle (5147)

  SELECT
      second.*
  FROM
      second p,
      second k
  WHERE
      p.id = k.first_id

  Which row in second is the SELECT clause referring to?
  • Re: (Score:1)

    by jplindstrom (594)

    Yeah, when you have ambiguities, you need to disambiguate. So? That's not the case in my example.
    
    And that's not what it complains about. It complains that it doesn't know of the table at all, not that it doesn't know which table I refer to.
    
    • Re: (Score:1)

      by Aristotle (5147)

      Hmm, I don’t have PostgreSQL handy here to check; but I know that SQLite at least behaves just like MySQL. I’d be surprised, actually, if any RDBMS let you do what you were after.

      We can quibble about the wording of the error message; I’ll agree that it could be more plausible.

      I also won’t disagree at all that MySQL is a cardhouse of hack upon hack.

      But… I dunno, that behaviour there seems sensible to me.
      • Re: (Score:1)

        by stu42j (6348)

        Here's what Postgres says:
        
        ERROR: invalid reference to FROM-clause entry for table "first"
HINT: Perhaps you meant to reference the table alias "f".

• May be standard (Score:2)

  by VSarkiss (704)

  I know that neither SQL Server nor Oracle will support that syntax. Once you alias a table name in a query, you must use the alias within the scope of that query.

  It may be part of the SQL standard. I'm not sure, and the document is a bear to grep thru.

  I agree the error message could be clearer.
  • Re: (Score:1)

    by avik (3911)

    I stumbled upon it too just the other day.
    But it makes sense, doesn't it?
    
    Imagine this
    select F.*, B.*
    from SERVER1.DB1.owner1.FOOFOOFOOFOOFOO_2005 F
    join SERVER2.DB2.owner2.BARBARBARBARBAR_2005 B
    on F.first_field = B.first_field
    and F.second_field = B.second_field
    and F.third_field = B.third_field
    and F.fourth_field = B.fourth_field
    and F.you_get_the_idea = 1
    where f.something = 'something'
        and b.another_thing != 'something'
    order by 1,2,3
    
    If address for table changes for some reason (and it happen