AM Thomas wrote:
Hi there,
I'll answer your questions below, but I should tell you that it looks
like even three or four joins seem to make this too slow.
Theoretically, the user could select up to 15 or so GoalNumber values,
12 for Grade, and about 20 possibilities for Subject - clearly it
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
I am guessing the long duration is caused by having to do complete table
scans. How big is your dataset?
What about creating another index in resource_goals that includes
GoalNumber and ResourceID? Perhaps even Subject, Grade, and
NumericGrade As I learned just a couple days ago, making
Jeremy Cole wrote:
Hi,
Thanks, but unless I'm missing something, that will return the same
as SUBJECT=English and (GRADE=1 or GRADE=2), which is resource
records 1,2,3, and 4 - too many matches. Am I missing something?
How about this:
SELECT resources.id, resources.title
FROM resources
LEFT
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,
Hi there,
I'll answer your questions below, but I should tell you that it looks like
even three or four joins seem to make this too slow. Theoretically, the
user could select up to 15 or so GoalNumber values, 12 for Grade, and
about 20 possibilities for Subject - clearly it wouldn't be
In article [EMAIL PROTECTED],
AM Thomas [EMAIL PROTECTED] 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(*)
, February 13, 2005 7:23 AM
To: mysql@lists.mysql.com
Subject: select where multiple joined records match
I'm trying to figure out how to select all the records in one table
which have multiple specified records in a second table. My MySQL is
version 4.0.23a, if that makes a difference.
Here's
@lists.mysql.com
Subject: select where multiple joined records match
I'm trying to figure out how to select all the records in one table
which have multiple specified records in a second table. My MySQL is
version 4.0.23a, if that makes a difference.
Here's a simplified version of my problem.
I
Hi,
Thanks, but unless I'm missing something, that will return the same as
SUBJECT=English and (GRADE=1 or GRADE=2), which is resource
records 1,2,3, and 4 - too many matches. Am I missing something?
How about this:
SELECT resources.id, resources.title
FROM resources
LEFT JOIN goals AS goal_a
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
I'm trying to figure out how to select all the records in one table
which have multiple specified records in a second table. My MySQL is
version 4.0.23a, if that makes a difference.
Here's a simplified version of my problem.
I have two tables, resources and goals.
resources table:
ID TITLE
1
Have a look at the manual page for EXISTS, you appear to need something like
SELECT * FROM resources AS r
WHERE EXISTS (
SELECT resource_id FROM goals AS g
WHERE g.resource_id = r.id AND grade=1 AND subject='English'
)
AND EXISTS (
SELECT resource_id FROM goals AS g
WHERE
AM Thomas wrote:
I'm trying to figure out how to select all the records in one table
which have multiple specified records in a second table. My MySQL is
version 4.0.23a, if that makes a difference.
Here's a simplified version of my problem.
I have two tables, resources and goals.
resources
Except that he/she is using 4.0, which doesn't support subqueries.
Michael
Peter Brawley wrote:
Have a look at the manual page for EXISTS, you appear to need something
like
SELECT * FROM resources AS r
WHERE EXISTS (
SELECT resource_id FROM goals AS g
WHERE g.resource_id = r.id AND grade=1
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
16 matches
Mail list logo