In the last episode (Jun 18), Edward Kay said: > From: Dan Nelson [mailto:[EMAIL PROTECTED] > > 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. > > Yes, that is true and it does work. > > What doesn't work however, is the extra 'AND is_primary = 0' HAVING > clause.
Couldn't you move that up into a WHERE clause (still in the subquery)? It's just a regular field comparison, so it doesn't have to be in the HAVING clause. -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]