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

[Arkem Paul]

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 don't like this table, you really should consider a unique primary key 
for this table (unless you're using a composite key of fieldID and 
objectID). Also value should probably be two fields since you're storing 
two different types of data in it.

> 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).
> SELECT objectId FROM thetable WHERE fieldId = 'bar' AND value = '1'
> AND objectId = (SELECT objectId FROM thetable WHERE fieldID = 'foo'
> AND value = 'Some text');

This statement below is what you want (don't take my word for it!) it 
performs a subquery to create a set of objectID that satisfies one 
condition and then uses a set membership check to test for the condition 
as part of the other conditions.

SELECT DISTINCT objectID FROM thetable
   WHERE fieldID = 'bar'
   AND value = '1'
   AND objectID IN (SELECT objectID
     FROM thetable WHERE fieldID = 'foo'
     AND value LIKE '__%')

This is untested but it returns objectID if (fieldID = 'bar' and 
value='1') and objectID is in the set of objectID that (fieldID = 'foo' 
and value is a string of at least 2 characters)

The '__%' is a mysql wildcard string search where _ represents exactly 1 
character and % represents any number of characters.

-Paul