On Wed, 2010-06-16 at 02:58 +0200, Jan Reiter wrote: > Hi folks! > > I'm kind of ashamed to ask a question, as I haven't followed this list very > much lately. > > > > This isn't exactly a PHP question, but since mysql is the most popular > database engine used with php, I figured someone here might have an idea. > > > > I have 2 tables. Table A containing 2 fields. A user ID and a picture ID => > A(uid,pid) and another table B, containing 3 fields. The picture ID, an > attribute ID and a value for that attribute => B(pid,aid,value). > > > > Table B contains several rows for a single PID with various AIDs and values. > Each AID is unique to a PID. (e.g. AID = 1 always holding the value for the > image size and AID = 3 always holding a value for the image type) > > > > The goal is now to join table A on table B using pid, and selecting the rows > based on MULTIPLE attributes. > > > > So the result should only contain rows for images, that relate to an > attribute ID = 1 (size) that is bigger than 100 AND!!!!!!! an attribute ID = > 5 that equals 'jpg'. > > > > I know that there is an easy solution to this, doing it in one query and I > have the feeling, that I can almost touch it with my fingertips in my mind, > but I can't go that final step, if you know what I mean. AND THAT DRIVES ME > CRAZY!!!!!! > > > > I appreciate your thoughts on this. > > > > Regards, > > Jan > You'll be looking for something like this (untested): SELECT * FROM a LEFT JOIN b ON (a.pid = b.pid) WHERE (b.aid = 1 AND b.value > 100) OR (b.aid = 3 AND b.value = 'jpg') Obviously instead of the * you may have to change to a list of field names to avoid fieldname collision on the two tables. Thanks, Ash http://www.ashleysheridan.co.uk
