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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
