MySQL Lists are EOL. Please join:

List:General Discussion« Previous MessageNext Message »
From:AM Thomas Date:February 21 2005 9:13pm
Subject:Re: select where multiple joined records match
View as plain text  
Such bounty of comments!  Thank you, Michael Stassen.

I see how my logic was faulty, and that a more correct solution would  
indeed be faster.  Thanks for pointing that out and not making me feel  
like too much of an idiot :)   I'll try the revised solution.  I am  
clueful about "join" making more rows/record, but didn't realize that it  
would be *that* dramatic.

As for the NumericGrade field, I'm basically getting the text grade (e.g.  
8th) from someone's MS Access CSV export; I figured I'd leave it in place,  
and use the numeric grade for sorting.  I'd never run into speed issues  
before, so I was just trying to save myself coding time by sticking with  
what I had already.  I wouldn't need the special lookup tables for grade  
and subject; if I were to use numeric fields, I could just do a lookup in  
Perl.  Next time, when I try to do this better from the start, I probably  
will.  At the moment, though, I'm trying to avoid changing the Perl code  
as much as I can (it's much recycled from an earlier project).

Will look into indexing - that's probably covered in my old O'Reilly  
MySQL/mSQL book.

I am curious about how much faster numeric field comparisons would be to  
string field comparisons for the Grade field; Would it make enough of a  
difference to this problem for me to go mucking with this Perl code?  This  
is a CGI Web app, so it's not lightning fast anyway, but then there are a  
lot of comparisons going on.  Currently it looks like it will have about  
300 resource records and about six thousand resource_goal records; I  
should test this myself...

I do have a lot of multi-subject and multi-grade resources; my testing was  
just not very good, I think.

Oh, and I think I see the error of my ways with regard to my TINYTEXT  
fields.  Probably would do well to shrink those.  Easy to change, too.

Thanks!  You've given excellent explanations here.  I feel like I should  
buy your book now, if you have one!

I'm wrestling with CSS issues on IE 4.0 for the Mac at the moment, but  
will return to SQL issues soon, I hope.

More later probably,
AM




On Mon, 21 Feb 2005 14:30:59 -0500, Michael Stassen  
<Michael.Stassen@stripped> wrote:

> As I understand it, you are looking for a resource for 4th grade Social  
> Studies which meets goals 1 through 4.  In terms of your tables, that  
> corresponds to having 4 rows in resources_goals, *all* of which have  
> grade=4th and Subject='Social_Studies'.  That is, we need to look in *4*  
> copies of resources_goals (not 5).  For a given resource id, we want  
> exactly one row from each copy, namely, the row with the correct  
> resource id, correct subject, correct grade, and desired goal number.  I  
> think this should do:
>
>    SELECT r.id
>    FROM resources as r
>    JOIN resource_goals as g1
>      ON  r.id = g1.ResourceID
>      AND g1.Subject = 'Social_Studies'
>      AND g1.Grade = '4th'
>      AND g1.GoalNumber = 1
>    JOIN resource_goals as g2
>      ON  r.id = g2.ResourceID
>      AND g2.Subject = 'Social_Studies'
>      AND g2.Grade = '4th'
>      AND g2.GoalNumber = 2
>    JOIN resource_goals as g3
>      ON  r.id = g3.ResourceID
>      AND g3.Subject = 'Social_Studies'
>      AND g3.Grade = '4th'
>      AND g3.GoalNumber = 3
>    JOIN resource_goals as g4
>      ON  r.id = g4.ResourceID
>      AND g4.Subject = 'Social_Studies'
>      AND g4.Grade = '4th'
>      AND g4.GoalNumber = 4;


-- 
Virtue of the Small / (919) 929-8687
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