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

[Craig Ringer]

On Thu, 2005-05-26 at 10:11 +0800, Andrew Furey wrote:
> I have a MySQL database containing a table with the following
> structure (CSVd for email):
> 
> fieldId, objectId, value
> -------
> foo, 101, "Some text"
> bar, 101, 1
> foo, 102, "Some text"
> bar, 102, 0
> foo, 103, "Another string"
> bar, 103, 1
> 
> I'm hoping to construct a _single query_ (the calling object doesn't
> have any processing capabilities as it stands, so no intermediate Perl
> variables etc) whereby I can return "101", since that is the only
> objectId that has "foo" equal to "Some text" AND "bar" equal to 1
> (boolean usage).

My brain hasn't really turned on yet, but I think you can cross-join the
table with its self - filtering on rows with equal objectId, then use a
more conventional where clause. I think. Hear the confidence.

-- 
Craig Ringer