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

[Andrew Furey]

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).

If it helps, the values "foo" and "bar" are fixed in this usage so
they can be hardcoded if needed. The best I've come up with is using a
subselect (in 4.1, which the database isn't currently so I'd have to
upgrade it):

SELECT objectId FROM thetable WHERE fieldId = 'bar' AND value = '1'
AND objectId = (SELECT objectId FROM thetable WHERE fieldID = 'foo'
AND value = 'Some text');

But that doesn't work as-is because the subselect returns two values
(101 and 102). I don't mind if the overall query returns multiple
(distinct) values if it's meant to...

Any ideas? Or is this a pipe dream?

TIA
Andrew

-- 
Linux supports the notion of a command line or a shell for the same
reason that only children read books with only pictures in them.
Language, be it English or something else, is the only tool flexible
enough to accomplish a sufficiently broad range of tasks.
                          -- Bill Garrett