MySQL Lists are EOL. Please join:

List:General Discussion« Previous MessageNext Message »
From:Michael Stassen Date:February 16 2005 4:08pm
Subject:Re: select where multiple joined records match
View as plain text  
AM Thomas wrote:

> Guarded exclamations of success!
> 
> This seems to be working for me so far - thank you!  Here's an actual  
> example of a working query on my database; the field names are a little  
> different (sorry, I was trying to make my earlier example more 
> readable).   The main change, though, is that I did plain 'join' instead 
> of 'left  join', which seems to make it much faster.  It was pretty slow 
> at first.

Yes, LEFT JOIN does extra work, and it wasn't needed here.

> mysql> select r.id, ga.Grade, ga.GoalNumber, gb.GoalNumber from 
> resources  as r
>        join resource_goals as ga on r.id=ga.ResourceID
>        and ga.Grade='4th' and ga.GoalNumber='1'
>        join resource_goals as gb on r.id=gb.ResourceID
>        and gb.Grade='4th' and gb.GoalNumber='2'
>        where ga.goal_id IS NOT NULL
>        and gb.goal_id IS NOT NULL
>        group by r.id;

There are some strange things here, I think.

* You've never shown us your table definitions, but I would have expected 
GoalNumber to be an integer, not a string.  If so, you shouldn't quote the 
numbers you compare it to.

* I think it unlikely that you have rows with values in Grade, GoalNumber, 
and ResourceID which have NULL for goal_id.  Isn't goal_id the primary key? 
  If I'm right, you don't need your WHERE clause.

* Why have you added "GROUP BY r.id"?  Was that an attempt to fix something? 
  If we've got the query right, there should be no need for grouping.  If 
you don't get the result you want without the GROUP BY, then you should let 
us know, because that would mean we've missed something.

Putting those together, I'd expect

   SELECT r.id, ga.Grade, ga.GoalNumber, gb.GoalNumber
   FROM resources AS r
   JOIN resource_goals AS ga
      ON r.id = ga.ResourceID
     AND ga.Grade = '4th'
     AND ga.GoalNumber = 1
   JOIN resource_goals AS gb
      ON r.id = gb.ResourceID
     AND gb.Grade = '4th'
     AND gb.GoalNumber = 2

to do the job.  Does it?

Michael

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