> -----Original Message----- > From: Dan Nelson [mailto:[EMAIL PROTECTED] > Sent: 15 June 2007 19:24 > To: Edward Kay > Cc: MySQL List > Subject: Re: Hiding columns used in GROUP BY and HAVING clauses > > > 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. Thanks anyway, Edward -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
