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
Try this
Select *
from resources, goals
where resources.ID = goals.RESOURCE_ID
and (SUBJECT=English
and GRADE=1)
OR
(SUBJECT=English
and GRADE=2);
-Original Message-
From: AM Thomas [mailto:[EMAIL PROTECTED]
Sent: Sunday,
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?
- AM
On Mon, 14 Feb 2005 12:30:44 -0600, Gordon [EMAIL PROTECTED] wrote:
Try this
Select *
from
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
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
13 matches
Mail list logo
