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

[Andrew Furey]

Simon wrote:
> SELECT t1.objectId FROM thetable t1 WHERE t1.fieldId = 'bar' AND
> t1.value = '1' AND t1.objectId = (SELECT t2.objectId FROM thetable t2 WHERE
> t2.fieldID = 'foo' AND t2.value = 'Some text' and t2.objectId = t1.objectId)

and

> 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'


Craig wrote:
> 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'


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


Yay, four different queries to use, all of which work... and Simon's second
(and Craig's functionally-the-same) don't use subselects so they still work
on the older MySQL. Happy days...


Arkem also wrote:
> 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.

Yes, it looks like fieldId and objectId are jointly the primary key.
Unfortunately I can't do anything about the table structure, it's part of an
existing web app. This also explains the nonsensical content - I'm using two
different field/value pairs to indicate related aspects of a single object. Yes,
it's idiotic, but it's the best I can do without writing a whole lot
of new code :(

Thanks all!
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