In the last episode (Jun 15), Edward Kay said: > I have a table of addresses. Each address is associated with a primary > entity and a primary entity can have n different addresses. For each primary > entity, one address is marked as the main address. > > I need a query to return all addresses that are the only address associated > with the primary entity but aren't marked as the main address. > > At the moment, I have this and it works: > > select * from contact_address > group by primary_entity_id > having count(primary_entity_id) = 1 > and is_primary = 0; > > This is fine except I want to use the result in a sub-query. Since it > returns two columns this doesn't work: > > update contact_address set is_primary = 1 where address_id in ( > select * from contact_address > group by primary_entity_id > having count(primary_entity_id) = 1 > and is_primary = 0 > ); > > Normally, I'd only return the address_id in the sub-SELECT, but I > need the is_primary column for the HAVING clause.
I did some tests, and it looks like you can use aggregate functions in your HAVING clause without actually selecting the column. So "HAVING COUNT(primary_entity_id) = 1" should work even if you only select address_id. -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]