[plug] OK, for all of you SQL gurus out there...

[Dean Holland]

I believe your first query, Craig, is the same as simon's. My 
understanding is equating columns between tables is an INNER JOIN, it's 
just a much simpler way of writing it. :)

Dean

Craig Ringer wrote:
> On Thu, 2005-05-26 at 02:47 +0000, simon wrote:
> 
>>actually, this may be more efficient - try both, it depends on lots of things.
>>
>>select t1.objectId from thetable t1, thetable t2
>>where t2.objectId = t1.objectId
>>and t1.fieldId = 'bar'
>>and t1.value = '1'
>>and t2.fieldId = 'foo'
>>and t2.value = 'Some text'
> 
> 
> Or, probably more efficient again:
> 
> SELECT t1.objectId
> FROM thetable t1 INNER JOIN thetable t2 ON t1.objectId = t2.objectId
> WHERE t1.fieldId = 'bar'
>      AND t1.value = '1'
>      AND t2.fieldId = 'foo'
>      AND t2.value = 'Some text'
> 
> and in PostgreSQL you can do:
> 
> 
> SELECT t1.objectId
> FROM thetable t1 INNER JOIN thetable t2 USING(objectId)
> WHERE t1.fieldId = 'bar'
>      AND t1.value = '1'
>      AND t2.fieldId = 'foo'
>      AND t2.value = 'Some text'
> 
> ... but I don't know if that's standard or if MySQL understands it.
>