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
-
From: AM Thomas [mailto:[EMAIL PROTECTED]
Sent: Sunday, 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. M
M Thomas [mailto:[EMAIL PROTECTED]
Sent: Sunday, 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
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
> HAV
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 ge
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
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
16 matches
Mail list logo
