Ronald,
I realize I'm splitting hairs here, and I'm no database expert, but I'm
curious about your answer to this - wouldn't this be even slightly more
efficient to write the WHERE clause conditions as most restricting
first? In other words,
SELECT feature.id
FROM feature,
reporter
WHERE reporter.attributes_id = ? <=== most restrictive 1st
AND feature.reporter_id = reporter.id <=== next most
restrictive
I was once told (or read?) that it is most efficient to put the most
restrictive conditions first in the WHERE - is that right? I've always
tended to put my joins towards the end of the WHERE when I have other
criteria that I'm looking for - just curious to know if I've been doing
it wrong.
Thanks.
Hardy Merrill
>>> "Ronald J Kimball" <[EMAIL PROTECTED]> 12/10/04 03:08PM >>>
kaustubh shinde [mailto:[EMAIL PROTECTED] wrote:
>
> Hi,
> I am using MySQL 4.0.21.
> Following is the problem:
> I want to
> Select ID from Reporter where Attributes_ID = something
> and then use the resultant IDs to find
> Select ID from Feature where Reporter_ID = the IDs found in above
> statement.
>
> Please note that the first query will give thousands of IDs and I
want to
> compare them all to the second one.
>
> It doesnt stop here. But if I get solution to this, rest shud be
easy.
> Now, I could probably use nested queries here. But MySQL 4.0 doesnt
> support
> it.
> I want to do this thru DBI and was wondering if its possible to get
result
> of a select query in a statement handle and use it to do a join with
some
> other table. If not, is there any other way to perform the above
> operations?
An inner join should work here, I think:
SELECT feature.id
FROM feature,
reporter
WHERE feature.reporter_id = reporter.id
AND reporter.attributes_id = ?
Ronald
