CODEWITHSUNDEEP
sqlinner-joinambiguous
Ben Pearce
Ben Pearce

Reputation: 7094

Querying the inner join of two tables with the same column name, Column 'exName' in field list is ambiguous

I am querying the inner join of three tables using the following query.

Two of the tables have columns named "name1". I am getting the following error. 

Column 'exName' in field list is ambiguous

The "name1" columns are foreign key so the information should be identical. Can anyone suggest a compact way around this?

$result = mysql_query("SELECT name1,name2,name3 FROM `table1` INNER JOIN `table2` ON table2.name1=table1.PrimaryKey INNER JOIN `table3` ON table3.name1=table1.PrimaryKey");

Upvotes: 10

Views: 99951

Answers (4)

Kulaba Brian Austin
Kulaba Brian Austin

Reputation: 339

Its true you have to separate the columns by using the table name. for example,

 SELECT table1.name1, table2.name1, etc.

As @dud3 explained the issue of the column name name1 from table1 table1.name1 overwriting the one in table2 table2.name1.

You can use the AS statement when selecting them so that you receive them separately in the array. E.g

SELECT table1.name1, table2.name1 AS table2_name1, etc.

You can use any different column name to represent table2_name1. Here you will be with different column names in the formed array.

Upvotes: 0

supergrady
supergrady

Reputation: 1322

You need to refer to the columns in your select list as:

SELECT <table name>.name1, <table name>.name2, <table name>.name3

You can also give the tables an alias when you introduce them in the from clause to save some keystrokes and make things cleaner:

SELECT 
     t1.name1
    ,t2.name2
    ,t3.name3
FROM table1 AS t1
INNER JOIN table2 AS t2
    ON t2.name1 = t1.PrimaryKey
INNER JOIN table3 AS t3
    ON t3.name1 = t1.PrimaryKey

Upvotes: 2

xQbert
xQbert

Reputation: 35323

SELECT T1.name1,T2.name2,T3.name3 
FROM `table1` T1 
INNER JOIN `table2` T2 ON t2.name1=t1.PrimaryKey 
INNER JOIN `table3` T3 ON t3.name1=t1.PrimaryKey

Upvotes: 0

Vincent Ramdhanie
Vincent Ramdhanie

Reputation: 103135

You need to qualify your column names with the table names.

 SELECT table1.name1, table2.name1, etc.

Upvotes: 18

Related Questions