List:General Discussion« Previous MessageNext Message »
From:Edward Kay Date:June 18 2007 9:26am
Subject:RE: Hiding columns used in GROUP BY and HAVING clauses
View as plain text  

> -----Original Message-----
> From: Dan Nelson [mailto:dnelson@stripped]
> 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
Thread
Hiding columns used in GROUP BY and HAVING clausesEdward Kay15 Jun
  • Re: Hiding columns used in GROUP BY and HAVING clausesBaron Schwartz15 Jun
  • Re: Hiding columns used in GROUP BY and HAVING clausesDan Nelson15 Jun
    • RE: Hiding columns used in GROUP BY and HAVING clausesEdward Kay18 Jun
      • Re: Hiding columns used in GROUP BY and HAVING clausesDan Nelson18 Jun
        • RE: Hiding columns used in GROUP BY and HAVING clausesEdward Kay18 Jun
          • Re: Hiding columns used in GROUP BY and HAVING clausesBaron Schwartz18 Jun
            • RE: Hiding columns used in GROUP BY and HAVING clausesEdward Kay18 Jun
          • Re: Hiding columns used in GROUP BY and HAVING clausesDan Nelson18 Jun
            • Re: Hiding columns used in GROUP BY and HAVING clausesBaron Schwartz18 Jun
              • RE: Hiding columns used in GROUP BY and HAVING clausesEdward Kay19 Jun