Column 'ID' in field list is ambiguous

Databases
1

Hi all.

My tables in DB MySQL:

doTable_A
ID	NAME_A
1	AAA
2	BBB


doTable_B
ID 	NAME_B
1	CCC
2	DDD

I need this output:

ID 	NAME
1	AAA
2	BBB
3	CCC
4	DDD

And this is my query:

SELECT
    DISTINCT (ID),
    (NAME_A)
FROM
    doTable_A
       LEFT OUTER JOIN
    doTable_B
       ON B.ID = A.ID

Response whit error:

Column ‘ID’ in field list is ambiguous

Why?

kind regards
viki

2

it’s because you have 2 tables with both a field id, so the engine is not sure which field to base it’s output on.

Prefix with a.id, or b.id, depending on which table you want the field be outputed


SELECT  
    DISTINCT (a.ID),  
    (a.NAME_A)  
FROM  
    doTable_A  as a
       LEFT OUTER JOIN 
    doTable_B as b 
       ON B.ID = A.ID
3

You have two tables both with an ID field.

Check out how the exact SQL syntax for the “AS” command.

This is from my failing memory :slight_smile:

SELECT
DISTINCT (fred.ID),
(NAME_A)
FROM
doTable_A as fred
LEFT OUTER JOIN
doTable_B as jack
ON B.ID = A.ID

4

Thanks for yours replies but with correct query I dont have this output:


ID 	NAME
1	AAA
2	BBB
3	CCC
4	DDD
5

using a JOIN, you’re not going to get that output. try a union instead:

select ID
     , name_A
  from doTable_A
 union all
select ID + (select max(id) from doTable_A)
     , name_B
  from doTable_B
6

Thanks, but I Have this error.

Microsoft OLE DB Provider for ODBC Drivers error ‘80004005’

[mysqld-5.0.45-community-nt-log]The used SELECT statements have a different number of columns

7

all the SELECTs in a UNION query have to return the same number of columns

maybe you could show us your real query?

8

Sorry my mistake… this query it’s OK. :slight_smile:
thanks x your help