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 mo
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 i
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 sur
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 use
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, i
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
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 plai
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 g
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
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: Sunda
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
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 tab
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 g.resource_i
13 matches
Mail list logo
