MySQL is returning an "column ambiguous" error when it used to work before I upgraded

I recently upgraded MySQL to 5.1.41. Before the upgrade the following SQL worked (or at least I thought I remembered it working...it has been a few weeks since designing this...). Now the SQL gives me an error stating that the "archived" column is ambiguous. How can I write this differently, or is there a different problem I'm not aware of?

I simply want to return the "unit_id", "lease_count" (stored in another table with a unit_id that should correspond with the "a.unit_id"), and "lease_archived_count (stored in another table with a unit_id that should correspond with the "a开发者_JAVA技巧.unit_id").

SELECT a.unit_id, 
    (SELECT count(*) FROM o_leases WHERE unit_id = a.unit_id AND archived = 0) as lease_count, 
    (SELECT count(*) FROM o_leases WHERE unit_id = a.unit_id AND archived = 1) as lease_archive_count

FROM p_unit a, properties b, portfolio c 

WHERE a.property_id = b.properties_id 
AND b.portfolio_id = c.portfolio_id 
AND a.archived = 0 

Thanks for your help.

There can only be one place where this error is referring to. I suggest to give the tables in the sub select also an alias:

(SELECT count(*) FROM o_leases o WHERE o.unit_id = a.unit_id AND o.archived = 0) as lease_count, 
(SELECT count(*) FROM o_leases o WHERE o.unit_id = a.unit_id AND o.archived = 1) as lease_archive_count

It seems that it collides with the archived field of p_unit.

I would guess the error is because o_leases is not the only table that has an archived field. p_unit Or properties or portfolio must have that column as well.

However you have several other things that should also be fixed. First in most databases correlated subqueries are poor performers and should be rewritten as joins (although in this case you probably only need a case statement and a join). You should notgenerally even consider writing correlated subqueries.

Second, you should NEVER use implicit joins. Extremely bad idea that leads to poor maintainability and accidental cross joins as well as a generally poor understanding of joins. Bad bad bad. This kind of code was replaced 18 years ago, would you still be using C# or Java code relaced witha better method even five years ago?