CODEWITHSUNDEEP
mysqlsql
tyrone 251
tyrone 251

Reputation: 339

mysql query ambiguous error

I am getting the error when i am trying to query this sql.

#1052 - Column 'charge_id' in where clause is ambiguous

Below is my query.

SELECT `inmate`.`inmate_id`,`inmate`.`fname`,`inmate`.`lname`,`inmate_case`.*,`case_information`.`case_id`,`case_charge`.*,`charge`.`charge_id` FROM inmate , `charge`
LEFT JOIN `prison`.`inmate_case` ON `inmate`.`inmate_id` = `inmate_case`.`inmate_id` 
LEFT JOIN `prison`.`case_information` ON `inmate_case`.`case_id` = `case_information`.`case_id` 
LEFT JOIN `prison`.`case_charge` ON `case_information`.`case_id` = `case_charge`.`case_id` 
 WHERE(( charge_id = 3))

Upvotes: 1

Views: 568

Answers (1)

Danny
Danny

Reputation: 1758

You must have multiple tables with a column named charge_id. Since you're putting it in your result set, you can change your WHERE clause to 

WHERE charge.charge_id = 3

If you only need the first and last name then you can drop some of the columns from your select - you must be pulling more than one column named charge_id (probably from case_charge). You can simplify the SELECT to something like:

SELECT inmate.inmate_id, inmate.fname, inmate.lname
FROM inmate
INNER JOIN prison.inmate_case ON inmate.inmate_id = inmate_case.inmate_id 
INNER JOIN prison.case_information ON inmate_case.case_id = case_information.case_id 
INNER JOIN prison.case_charge ON case_information.case_id = case_charge.case_id
INNER JOIN charge ON case_charge.charge_id = charge.charge_id
 WHERE charge.charge_id = 3

and add columns to your SELECT explicitly, as needed. I changed your LEFT JOINs to INNER JOINs since you only want inmates that do appear in the charge table. You would use OUTER JOIN if you're trying to pull inmates that MAY or MAY NOT appear in the other tables. (Although they would still work in this case since you're putting the end field in the WHERE clause. Without that you would return all inmates, even if they have no cases or charges at all).

Upvotes: 3

Related Questions