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

[simon]

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'



Andrew Furey (andrew.furey at gmail.com) 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).
>
> 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
>
> _______________________________________________
> PLUG discussion list: plug at plug.org.au
> http://www.plug.org.au/mailman/listinfo/plug
> Committee e-mail: committee at plug.linux.org.au
>

--
=================
Simon Scott
simon at plumtek.com
mob: 0409113359
=================