Re: Maybe easy, maybe hard SELECT puzzle :)

[Michael Stassen]

Jonathan Terhorst wrote:

I can't figure out if this is actually challenging or if it's a stupid
question. Table1 is a normal old relation that describes a bunch of objects:
Table1 (id INT PRIMARY KEY NOT NULL, Name varchar(255), Size int) etc.
Associated with each Table1 record is a variable-length of 3-byte
alphanumeric feature codes e.g. "A20". I've put these into table called
Features, that is,
Features (id INT PRIMARY KEY NOT NULL REFERENCES Table1, FeatureCode char(3)
NOT NULL);
So a typical record from Table1 might look like:
id: 123
Name: Foo
Size: 42
And the corresponding Features entries:
id: 123
FeatureCode: A01
----
id: 123
FeatureCode: Z99
----
id: 123
FeatureCode: X42
My question is how to pull all records from Table1 that have certain
FeatureCodes associate with them. If it's just one FeatureCode (say, 'T56')
I'm looking for then it's easy:
SELECT * from Table1 JOIN Features USING(id) WHERE
Features.FeatureCode='T56';
But it I want all records from Table1 that have features 'A01' _and_ 'B02',
clearly
SELECT * from Table1 JOIN Features USING(id) WHERE
Features.FeatureCode='A01' AND Features.FeatureCode='B02' doesn't work.
One way I have found to implement this is
SELECT Table1.id from Table1 JOIN Features USING(id) WHERE
Features.FeatureCode='A01' OR Features.FeatureCode='B02' GROUP BY Table1.id
HAVING COUNT(*)=2;
e.g. counting the duplications of id and selecting those equal to the number
of FeatureCodes I'm searching for. But this seems somehow inelegant, and I'm
justing wondering if there's a better way that's staring me in the face.
Thanks
My first thought whaen I saw your question was that you could join the 
Features table with itself on the id, looking for one feature on the 
left and the other feature on the right.  Something like this:

  SELECT Table1.id AS id
  FROM Features AS f1, Features AS f2, Table1
  WHERE f1.id=f2.id AND f1.id=Table1.id
  AND f1.FeatureCode='A01' AND f2.FeatureCode='B02';
Whether you would call that better or elegant relative to the solution 
you've already found is up to you.

You could also modify your version to use IN, as suggested by Shane 
Allen (I don't think DISTINCT will help you, but I could be wrong). 
Then it would look like this:

  SELECT Table1.id from Table1 JOIN Features USING(id)
  WHERE Features.FeatureCode IN ('A01', 'B02')
  GROUP BY Table1.id
  HAVING COUNT(*)=2;
Perhaps that looks better/more elegant?

In particular, if you want to do this programmatically for any number of 
features, this way is probably easier.  For example, to find the ids in 
Table1 with 3 specified features, the first version above would be

  SELECT Table1.id AS id
  FROM Features AS f1, Features AS f2, Features AS f3, Table1
  WHERE f1.id=f2.id AND f2.id=f3.id AND f1.id=Table1.id
  AND f1.FeatureCode='A01' AND f2.FeatureCode='B02'
  AND f3.FeatureCode='Z99';
Whereas the second version would become

  SELECT Table1.id from Table1 JOIN Features USING(id)
  WHERE Features.FeatureCode IN ('A01', 'B02', 'Z99')
  GROUP BY Table1.id
  HAVING COUNT(*)=3;
As you can see, the latter generalizes well to

  SELECT Table1.id from Table1 JOIN Features USING(id)
  WHERE Features.FeatureCode IN (list of FeatureCodes)
  GROUP BY Table1.id
  HAVING COUNT(*)=#_of_FeatureCodes_in_list;
I suspect this may be more efficient than the multiple join way, as 
well, but I wouldn't swear to it without testing first.

Michael

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]