MySQL Lists are EOL. Please join:

List:General Discussion« Previous MessageNext Message »
From:AM Thomas Date:February 14 2005 3:38am
Subject:more complexity (was: select where multiple joined records match)
View as plain text  
Thank you kindly for your gracious help.

I am making much progress (the 'as r' and 'as g' syntax is helpful; I'd  
forgotten about it since I'm doing most of this through Perl; thanks).  I  
played with the COUNT solution for a while and was initially encouraged,  
nay, ecstatic.

However, I was getting weird results and realized that I had left out
a wrinkle in my example (and my thinking): the extra field in my
goals table means that the other values can, as a group, be repeated
for several rows.  This seems to keep this solution from working.  Mea
culpa; my example wasn't adequate.

There might be multiple records with a given subject and grade
combination, due to the additional field (called "GoalNumber") in the
goals table.  The combination of ResourceID, Subject, Grade, and
GoalNumber will be unique, but the combination of ResourceID,
Subject, and Grade need not be.

Here's a revision of my example.

resources table:

ID  TITLE
1   Got Your Nose
2   Goats and Waterfowl: A Promising Alliance
3   North Carolina and WWIIb
4   Geodesic Domes - Ivy Revolution


goals table:

(I know all the numbers make it hard to read, sorry; I'll try to
improve readability by putting a blank line before a new RESOURCE_ID).

ID RESOURCE_ID  GRADE  GOALNUMBER  SUBJECT
  1  1            1      1           English
  2  1            1      2           English
  3  1            1      3           English
  4  1            1      1           Soc
  5  1            2      5           English
  6  1            2      6           English
  7  1            2      1           English
  8  1            2      2           English

  9  2            1      1           English
10  2            3      1           Soc

11  3            2      1           English
12  3            2      7           English
13  3            2      9           English

14  4            1      1           English


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).

I wish the EXISTS solution offered by Mr. Brawley would work, but
thanks to Mr. Stassen for the clue about MySQL version.  I tried it
anyway at the command line but, of course, it didn't work.

I also found a reference to an INTERSECTION keyword and experimented
with it briefly; I couldn't find a document that listed when certain
features came into MySQL, so I don't know if INTERSECTION is
completely out of the picture, though UNION seems to have arrived
after 4.0.  Is there such a document?  Or do I have to look at the
change log for each version?

( Running "mysql -V" actually gives me: Ver 12.22 Distrib 4.0.23a )

- AM Thomas
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