Ambiguous column name error.

Hi all,

Running the query below, I get the error "column 'RecordID' in field
list is ambiguous". However (as you can see), RecordID isn't even in
the field list. The field does exist in the tables ut_maindata_tbl,
ft_maindata_tbl and entry_maindata_tbl (it's an auto-incrementing ID
field which is also the primary key). I'm using MySQL 5.0.18 on a
Windows 2000 server box.

SELECT entry_maindata_tbl.index_serial, ft_maindata_tbl.unit_serial,
ut_maindata_tbl.filename AS ut_filename, ft_maindata_tbl.filename AS
ft_filename, ut_maindata_tbl.cell,
ut_maindata_tbl.ut_compensated_tr_reg,
ut_maindata_tbl.ut_compensated_5r_reg,
ut_maindata_tbl.ut_tr_peak_pressure,
ut_maindata_tbl.ut_5r_peak_pressure, ft_maindata_tbl.test_line &
ft_maindata_tbl.final_test_head AS ft_head_id,
ft_maindata_tbl.max_pressure_loss, ft_maindata_tbl.tr_reg_err,
ft_maindata_tbl.5r_reg_error, ft_maindata_tbl.avg_tr_pressure_loss,
ft_maindata_tbl.5r_max_ploss, "output_passfirst" AS source_query,
entry_maindata_tbl.filename AS entry_filename,
ut_maindata_tbl.datetime AS UTDateTimeTested,
ft_maindata_tbl.ft_start_datetime AS FTDateTimeTested
FROM (entry_maindata_tbl INNER JOIN ut_maindata_tbl ON
entry_maindata_tbl.unit_serial = ut_maindata_tbl.unit_serial) INNER
JOIN ft_maindata_tbl ON ut_maindata_tbl.unit_serial =
ft_maindata_tbl.unit_serial
WHERE (((ft_maindata_tbl.unit_serial)=entry_maindata_tbl.unit_seri al)
AND ((ut_maindata_tbl.datetime)=ft_maindata_tbl.datetime)
AND ((ft_maindata_tbl.record_type)<>5 And (ft_maindata_tbl.record_type)<>7)
AND ((entry_maindata_tbl.datetime) Between
ft_maindata_tbl.tr_datetime-1 And ft_maindata_tbl.tr_datetime+1)
AND ((ft_maindata_tbl.pretest_pareto_flag)="0000000000000000" Or
(ft_maindata_tbl.pretest_pareto_flag)="0000000000010000")
AND ((ft_maindata_tbl.ft_pareto_flag)="0000000000000000000000000 0000000"))

Any ideas? I'm guessing it's something to do with me linking the
tables, but all the tables having a primary key called "RecordID".

Thanks in advance,
Rgds,
Chris.


--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=gcdmw-win32@m.gmane.org

Re: Ambiguous column name error.

--=_alternative 0053F90B85257130_=
Content-Type: text/plain; charset="US-ASCII"

Chris Robinson wrote on 03/13/2006
09:50:36 AM:

> Hi all,
>
> Running the query below, I get the error "column 'RecordID' in field
> list is ambiguous". However (as you can see), RecordID isn't even in
> the field list. The field does exist in the tables ut_maindata_tbl,
> ft_maindata_tbl and entry_maindata_tbl (it's an auto-incrementing ID
> field which is also the primary key). I'm using MySQL 5.0.18 on a
> Windows 2000 server box.
>
> SELECT entry_maindata_tbl.index_serial, ft_maindata_tbl.unit_serial,
> ut_maindata_tbl.filename AS ut_filename, ft_maindata_tbl.filename AS
> ft_filename, ut_maindata_tbl.cell,
> ut_maindata_tbl.ut_compensated_tr_reg,
> ut_maindata_tbl.ut_compensated_5r_reg,
> ut_maindata_tbl.ut_tr_peak_pressure,
> ut_maindata_tbl.ut_5r_peak_pressure, ft_maindata_tbl.test_line &
> ft_maindata_tbl.final_test_head AS ft_head_id,
> ft_maindata_tbl.max_pressure_loss, ft_maindata_tbl.tr_reg_err,
> ft_maindata_tbl.5r_reg_error, ft_maindata_tbl.avg_tr_pressure_loss,
> ft_maindata_tbl.5r_max_ploss, "output_passfirst" AS source_query,
> entry_maindata_tbl.filename AS entry_filename,
> ut_maindata_tbl.datetime AS UTDateTimeTested,
> ft_maindata_tbl.ft_start_datetime AS FTDateTimeTested
> FROM (entry_maindata_tbl INNER JOIN ut_maindata_tbl ON
> entry_maindata_tbl.unit_serial = ut_maindata_tbl.unit_serial) INNER
> JOIN ft_maindata_tbl ON ut_maindata_tbl.unit_serial =
> ft_maindata_tbl.unit_serial
> WHERE (((ft_maindata_tbl.unit_serial)=entry_maindata_tbl.unit_seri al)
> AND ((ut_maindata_tbl.datetime)=ft_maindata_tbl.datetime)
> AND ((ft_maindata_tbl.record_type)<>5 And
(ft_maindata_tbl.record_type)<>7)
> AND ((entry_maindata_tbl.datetime) Between
> ft_maindata_tbl.tr_datetime-1 And ft_maindata_tbl.tr_datetime+1)
> AND ((ft_maindata_tbl.pretest_pareto_flag)="0000000000000000" Or
> (ft_maindata_tbl.pretest_pareto_flag)="0000000000010000")
> AND
((ft_maindata_tbl.ft_pareto_flag)="0000000000000000000000000 0000000"))
>
> Any ideas? I'm guessing it's something to do with me linking the
> tables, but all the tables having a primary key called "RecordID".
>
> Thanks in advance,
> Rgds,
> Chris.
>

Same query, unwrapped:

SELECT entry_maindata_tbl.index_serial
, ft_maindata_tbl.unit_serial
, ut_maindata_tbl.filename AS ut_filename
, ft_maindata_tbl.filename AS ft_filename
, ut_maindata_tbl.cell
, ut_maindata_tbl.ut_compensated_tr_reg
, ut_maindata_tbl.ut_compensated_5r_reg
, ut_maindata_tbl.ut_tr_peak_pressure
, ut_maindata_tbl.ut_5r_peak_pressure
, ft_maindata_tbl.test_line & ft_maindata_tbl.final_test_head AS
ft_head_id
, ft_maindata_tbl.max_pressure_loss
, ft_maindata_tbl.tr_reg_err
, ft_maindata_tbl.5r_reg_error
, ft_maindata_tbl.avg_tr_pressure_loss
, ft_maindata_tbl.5r_max_ploss
, "output_passfirst" AS source_query
, entry_maindata_tbl.filename AS entry_filename
, ut_maindata_tbl.datetime AS UTDateTimeTested
, ft_maindata_tbl.ft_start_datetime AS FTDateTimeTested
FROM (
entry_maindata_tbl
INNER JOIN ut_maindata_tbl
ON entry_maindata_tbl.unit_serial =
ut_maindata_tbl.unit_serial
)
INNER JOIN ft_maindata_tbl
ON ut_maindata_tbl.unit_serial = ft_maindata_tbl.unit_serial
WHERE (
((ft_maindata_tbl.unit_serial)=entry_maindata_tbl.unit_seria l)
AND ((ut_maindata_tbl.datetime)=ft_maindata_tbl.datetime)
AND ((ft_maindata_tbl.record_type)<>5
And (ft_maindata_tbl.record_type)<>7)
AND ((entry_maindata_tbl.datetime) Between
ft_maindata_tbl.tr_datetime-1 And ft_maindata_tbl.tr_datetime+1)
AND ((ft_maindata_tbl.pretest_pareto_flag)="0000000000000000"
Or
(ft_maindata_tbl.pretest_pareto_flag)="0000000000010000")
AND
((ft_maindata_tbl.ft_pareto_flag)="0000000000000000000000000 0000000"))


This logic seems a bit convoluted to me:

FROM (
entry_maindata_tbl
INNER JOIN ut_maindata_tbl
ON entry_maindata_tbl.unit_serial =
ut_maindata_tbl.unit_serial
)
INNER JOIN ft_maindata_tbl
ON ut_maindata_tbl.unit_serial = ft_maindata_tbl.unit_serial
WHERE (
((ft_maindata_tbl.unit_serial)=entry_maindata_tbl.unit_seria l)
AND ((ut_maindata_tbl.datetime)=ft_maindata_tbl.datetime)
AND ((ft_maindata_tbl.record_type)<>5
And (ft_maindata_tbl.record_type)<>7)
AND ((entry_maindata_tbl.datetime) Between
ft_maindata_tbl.tr_datetime-1 And ft_maindata_tbl.tr_datetime+1)
AND ((ft_maindata_tbl.pretest_pareto_flag)="0000000000000000"
Or
(ft_maindata_tbl.pretest_pareto_flag)="0000000000010000")
AND
((ft_maindata_tbl.ft_pareto_flag)="0000000000000000000000000 0000000"))

I think that this is a logical equivalent (based on transitive equalities:
if A=B and B=C then A=C)
FROM ft_maindata_tbl
INNER JOIN entry_maindata_tbl
ON entry_maindata_tbl.unit_serial = ft_maindata_tbl.unit_serial
AND entry_maindata_tbl.datetime Between
ft_maindata_tbl.tr_datetime-1
And ft_maindata_tbl.tr_datetime+1
INNER JOIN ut_maindata_tbl
ON entry_maindata_tbl.unit_serial = ut_maindata_tbl.unit_serial
AND ut_maindata_tbl.datetime=ft_maindata_tbl.datetime
WHERE (
ft_maindata_tbl.record_type <5
OR ft_maindata_tbl.record_type >7
OR ft_maindata_tbl.recordtype=6
)
AND (
ft_maindata_tbl.pretest_pareto_flag="0000000000000000"
Or ft_maindata_tbl.pretest_pareto_flag="0000000000010000"
)
AND
ft_maindata_tbl.ft_pareto_flag="0000000000000000000000000000 0000"


If you change your