MySQL Query Failed: Column 'candidate_ID' in field list is ambiguous?

Question

I'm having an issue with the following error but can work out why?

MySQL Query Failed: Column 'candidate_ID' in field list is ambiguous

SELECT SQL_CALC_FOUND_ROWS 
            candidate.candidate_id AS candidateID,
            candidate.candidate_id AS exportID,
            candidate.is_hot AS isHot,
            candidate.date_modified AS dateModifiedSort,
            candidate.date_created AS dateCreatedSort,
        candidate_ID AS candidateID,
candidate.first_name AS firstName,
candidate.last_name AS lastName,
extra_field0.value AS extra_field_value0,
candidate.city AS city,
candidate.desired_pay AS desiredPay,
candidate.email1 AS email1,
candidate.phone_cell AS phoneCell,
DATE_FORMAT(candidate.date_modified, '%d-%m-%y') AS dateModified,
IF(candidate_joborder_submitted.candidate_joborder_id, 1, 0) AS submitted,
                                            IF(attachment_id, 1, 0) AS **strong text**attachmentPresent
        FROM
            candidate
        LEFT JOIN extra_field AS extra_field0 ON candidate.candidate_id = extra_field0.data_item_id AND extra_field0.field_name = 'Job Title' AND extra_field0.data_item_type = 100
LEFT JOIN attachment
                                                    ON candidate.candidate_id = attachment.data_item_id
                                      AND attachment.data_item_type = 100
                                                LEFT JOIN candidate_joborder AS candidate_joborder_submitted
                                                    ON candidate_joborder_submitted.candidate_id = candidate.candidate_id
                                                    AND candidate_joborder_submitted.status >= 400
                                                    AND candidate_joborder_submitted.site_id = 1
                                                    AND candidate_joborder_submitted.status != 650 LEFT JOIN saved_list_entry
                                ON saved_list_entry.data_item_type = 100
                                AND saved_list_entry.data_item_id = candidate.candidate_id
                                AND saved_list_entry.site_id = 1
        WHERE
            candidate.site_id = 1



        GROUP BY candidate.candidate_id

        ORDER BY dateModifiedSort DESC
        LIMIT 0, 15

Any help be greatly appreciated in my hour of need

Specify the table here `sometable.candidate_ID AS candidateID` — Mihai, Oct 26 '16 at 19:16

score 1 · Answer 1 · edited Oct 26 '16 at 19:22

1

You have candidate_ID AS candidateID, without a table name. Due to the fact that you have candidate_ID in two different tables, you must specify the table name:

  candidate.candidate_ID AS candidateID,

This avoids the ambiguity

edited Oct 26 '16 at 19:22

Sharlike

1,789
2
19
30

answered Oct 26 '16 at 19:16

ScaisEdge

131,976
10
91
107

Roger Worrell · Answer 2 · 2016-10-26T19:25:17.260

1

You have an unqualified candidate_ID as your sixth item.

candidate_ID AS candidateID,

Should be

candidate.candidate_ID as candidateID

Due to the fact that you already have candidateID defined with candidate.candidate_id, I would recommend removing "candidate_ID AS candidateID" from your query completely.

edited Oct 26 '16 at 19:25

answered Oct 26 '16 at 19:17

Roger Worrell

64
5

score 0 · Answer 3 · answered Oct 26 '16 at 19:16

If you look closely at the error it tells you.

MySQL Query Failed: Column 'candidate_ID' in field list is ambiguous

In the field list you need to specify the table for candidate_ID, like your other fields.

candidate.candidate_ID as 'candidateID',

It is ambiguous because candidate_ID exists in multiple tables from your query.

MySQL Query Failed: Column 'candidate_ID' in field list is ambiguous?

3 Answers3