Difference between revisions of "Ambigous column name"

From SQLZOO
Jump to: navigation, search
(Created page with "<div class='err'> <div class=params>schema:gisq</div> <div class = "link e-oracle>ORA-00918: column ambiguously defined</div> <div class = "link e-mysql>Error 1052 Column 'nam...")
 
Line 1: Line 1:
 
<div class='err'>
 
<div class='err'>
 
<div class=params>schema:gisq</div>
 
<div class=params>schema:gisq</div>
<div class = "link e-oracle>ORA-00918: column ambiguously defined</div>
+
<div class = "link e-oracle">ORA-00918: column ambiguously defined</div>
<div class = "link e-mysql>Error 1052
+
<div class = "link e-mysql">Error 1052
 
Column 'name' in field list is ambiguous</div>
 
Column 'name' in field list is ambiguous</div>
<div class = "link e-sqlite>ambiguous column name:</div>
+
<div class = "link e-sqlite">ambiguous column name:</div>
<div class = "link e-postgres>Error 7
+
<div class = "link e-postgres">Error 7
 
ERROR: Column reference "name" is ambiguous</div>
 
ERROR: Column reference "name" is ambiguous</div>
<div class = "link e-db2>SQL0203N A reference to column "NAME" is ambiguous. SQLSTATE=42702 </div>
+
<div class = "link e-db2">SQL0203N A reference to column "NAME" is ambiguous. SQLSTATE=42702 </div>
<div class = "link e-sqlserver>Msg 1013,
+
<div class = "link e-sqlserver">Msg 1013,
 
Ambiguous column name 'name'.</div>
 
Ambiguous column name 'name'.</div>
  

Revision as of 12:31, 19 July 2012

schema:gisq

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