Ambigous column name

From SQLZOO
Revision as of 12:15, 19 July 2012 by Marek (Talk | contribs)

(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)
Jump to: navigation, search
schema:gisq
ORA-00918: column ambiguously defined
Error 1052 Column 'name' in field list is ambiguous
ambiguous column name:
Error 7 ERROR: Column reference "name" is ambiguous
SQL0203N A reference to column "NAME" is ambiguous. SQLSTATE=42702
Msg 1013, Ambiguous column name 'name'.

Problem

When more than one table is used (in a JOIN for example) there may be two columns with the same name.

In this example there is a column called name

in bbc and also in cia.

Solutions

  • Include the table name before the column name:
    SELECT bbc.name
      FROM bbc JOIN cia ON bbc.name = cia.name
    
  • Alias at least one of the tables and use that
    SELECT B.name
    FROM bbc B JOIN cia ON B.name = cia.name
 
 
SELECT name
  FROM bbc JOIN cia ON bbc.name = cia.name
Personal tools
Namespaces

Variants
Actions
Reference
Toolbox
Google AdSense