List:General Discussion« Previous MessageNext Message »
From:James Northcott / Chief Systems Date:November 28 2006 3:06pm
Subject:Re: Many-Many relation, matching all
View as plain text  
Peter Brawley wrote:
> >I want to find all A's such that
> >they have exactly B's 1 and 2
> >SELECT A.ID, group_concat(BID ORDER BY BID) as Bs
> >FROM A INNER JOIN AhasB ON A.ID=AID
> >GROUP BY A.ID
> >HAVING Bs='1,2'
>
> Why the join? Doesn't your ahasb bridge table already incorporate the 
> join logic? If your requirement is to retrieve all aid's with exactly 
> one instance of bid=1, exactly one with bid=2, and no other bid's, why 
> not just ...
>
> SELECT aid,GROUP_CONCAT(bid) AS list
> FROM ahasb
> GROUP BY aid
> HAVING list='1,2';
>
> PB
>
I actually need some of the other columns from A, but you're correct, 
this does work.  I did discover though that the ORDER BY in the 
group_concat is important, since MySQL doesn't always pick the same 
order for the list.

ddevaudreuil@stripped wrote:
> I think this will work:
>
> SELECT AID
> FROM AhasB 
> WHERE BID in (1,2)
> GROUP BY AID
> HAVING count(BID) =2
>
>
> Donna
>   

Thank you, this is actually very helpful.  The where clause uses the 
index I have in the table to screen out many more rows early in the 
query, and it also very nicely avoids the string compare on the 
group_concat.  I also wasn't aware that you could use an aggregate 
function in the HAVING clause without it appearing in the SELECT clause.

Thanks again, this solves the problem quite elegantly, and I probably 
never would have thought of it.
Thread
Many-Many relation, matching allJames Northcott / Chief Systems27 Nov
  • Re: Many-Many relation, matching allPeter Brawley28 Nov
    • Re: Many-Many relation, matching allJames Northcott / Chief Systems28 Nov
      • Re: Many-Many relation, matching allPeter Brawley28 Nov
        • Re: Many-Many relation, matching allddevaudreuil28 Nov
  • Re: Many-Many relation, matching allddevaudreuil28 Nov