MySQL Lists are EOL. Please join:

List:General Discussion« Previous MessageNext Message »
From:Harald Fuchs Date:February 14 2005 10:20am
Subject:Re: more complexity (was: select where multiple joined records match)
View as plain text  
In article <opsl54emgykj9464@stripped>,
"AM Thomas" <am@stripped> writes:

> Now, if I understand how this is working:

> SELECT r.TITLE
>     FROM resources r JOIN goals g ON (r.ID=g.RESOURCE_ID)
>     WHERE g.SUBJECT = 'English'
>       AND (g.GRADE = 1 OR g.GRADE = 2)
>     GROUP BY r.ID
>     HAVING COUNT(*) = 2;

> will give an incorrect result, because the number of rows returned for
> each matching ID will be unpredictable.  It could be 7 rows for ID =
> 1 (which is a correct match), or 3 rows for ID = 3 (which shouldn't
> match since it only has grade 2).

How about "HAVING count(DISTINCT g.grade) = 2"?

Thread
select where multiple joined records matchAM Thomas13 Feb
  • Re: select where multiple joined records matchPeter Brawley13 Feb
    • Re: select where multiple joined records matchMichael Stassen13 Feb
  • Re: select where multiple joined records matchMichael Stassen13 Feb
    • more complexity (was: select where multiple joined records match)AM Thomas14 Feb
  • RE: select where multiple joined records matchGordon14 Feb
    • Re: select where multiple joined records matchAM Thomas14 Feb
      • Re: select where multiple joined records matchJeremy Cole14 Feb
        • Re: select where multiple joined records matchAM Thomas15 Feb
          • Re: select where multiple joined records matchMichael Stassen16 Feb
            • Re: select where multiple joined records matchAM Thomas16 Feb
              • Re: select where multiple joined records matchMathew Ray17 Feb
              • Re: select where multiple joined records matchMichael Stassen21 Feb
                • Re: select where multiple joined records matchAM Thomas21 Feb
        • Re: select where multiple joined records matchMichael Stassen16 Feb
Re: more complexity (was: select where multiple joined records match)Harald Fuchs14 Feb