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

[Craig Ringer]

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.

-- 
Craig Ringer