in reply to Re^3: More MySQL woes
in thread More MySQL woes

If you ask me it should match up the rows by default. Either way, hopefully this'll save someone else from a lot of stress

If you mean automatically join tables based on the name of the fields -- Oracle does, and it caused me no end of headaches, because it requires significant planning when designing your tables to ensure that you never use the same field name for more than one purpose. 'id', as the primary key for the table? Nope. 'name' or 'abbr' in tables used in lookup tables? Nope.

It might be useful in same situations, but when the RDBMS starts making incorrect assumptions, it's extremely aggravating.

Replies are listed 'Best First'.
Re^5: More MySQL woes
by chrism01 (Friar) on Jan 21, 2008 at 05:05 UTC
    You should always use the format
    tablename.colname
    for statements involving more than one table, otherwise the DB has to guess which one you mean when the tables have the same named column.
    In fact, use a table alias for shorter code: 

    select t1.col1
from table1 t1, table2 t2
where t1.col1 = t2.col1

    [download]
[reply]
[d/l]
      Right, but my reading of jhourcle's post is that if you have columns named "id" in both tables, Oracle will automatically say "Oh, look - two columns with the same name! I'll be helpful and automatically JOIN them!" If this is the case (I'm not that familiar with Oracle, so I may be misunderstanding what he meant), then it doesn't matter how you refer to them in your query, the DB's misguided attempt to be "helpful" will still cause problems.
[reply]

In Section Seekers of Perl Wisdom