1

I have this query:

SELECT EquipmentNumber, EquipmentType, P.AreaCode AS Location, EquipDsc FROM MajorEquipment ME
 INNER JOIN PlantAreaCodes P ON ME.PACId = P.PACId WHERE
 (@EquipNumber IS NULL OR EquipmentNumber LIKE @EquipNumber)
 AND ((SELECT PACId FROM PlantAreaCodes WHERE AreaCode = @Location) IS NULL OR PACId = (SELECT PACId FROM PlantAreaCodes WHERE AreaCode = @Location))
 AND (@EquipType IS NULL OR EquipmentType LIKE @EquipType)
 AND (@Comments IS NULL OR EquipDsc LIKE @Comments);

And I get the error "Column 'PACId' in where clause is ambiguous."

I have tried this query before, with other data and I had no issues. What's going on?

Please let me know if you need more information.

Ben
  • 2,433
  • 5
  • 39
  • 69
  • You should prealiacing your ambiguous columns, `sql` does not know, what are you want `ME.PACId` or `P.PACId`. – vp_arth Jul 29 '14 at 05:08
  • 1
    Although you select PACId from PlantAreaCodes, you still need to explicitly state which table you are using. – Nick Tucci Jul 29 '14 at 05:09
  • possible duplicate of [MySQL: "Column 'column\_name' in where clause is ambiguous"](http://stackoverflow.com/questions/337034/mysql-column-column-name-in-where-clause-is-ambiguous) – vp_arth Jul 29 '14 at 05:10
  • @vp_arth I had a look at that, but I still couldn't quite understand what the issue was. – Ben Jul 29 '14 at 05:11
  • @Adherence did I not? I though `FROM MajorEquipment` clarified that? – Ben Jul 29 '14 at 05:12
  • 1
    `) IS NULL OR PACId = (SELECT` => `) IS NULL OR ME.PACId = (SELECT` – vp_arth Jul 29 '14 at 05:12
  • 2
    Change: `AND ((SELECT PACId FROM PlantAreaCodes WHERE AreaCode = @Location) IS NULL OR PACId = (SELECT PACId FROM PlantAreaCodes WHERE AreaCode = @Location))` to: `AND ((SELECT PACId FROM PlantAreaCodes WHERE AreaCode = @Location) IS NULL OR ME.PACId = (SELECT PACId FROM PlantAreaCodes WHERE AreaCode = @Location))` – Nir Alfasi Jul 29 '14 at 05:12
  • @vp_arth Ohhhh... right I see there. Thanks so much! If you provide that as an answer I'll mark it as accepted. Ty – Ben Jul 29 '14 at 05:15
  • ok, I post it below :) – vp_arth Jul 29 '14 at 05:18

1 Answers1

2

You should prealiacing your ambiguous columns,
sql does not know, what are you want ME.PACId or P.PACId.

Just change ) IS NULL OR PACId = (SELECT => ) IS NULL OR ME.PACId = (SELECT

vp_arth
  • 14,461
  • 4
  • 37
  • 66