Thanks. That was my first attempt, too. Only this will throw out rows, that meet only one of the conditions, too. For example, I would get all pictures that are bigger than 100, regardless of type, and all pictures that are of type jpg, no matter the size.
Doing it with a view would be an option, but that would immensely decrease flexibility. I guess I have to keep on cooking my brain on this ;-) I think I did it before, a few years ago when MySQL didn't support views yet, but I can't find that stuff ... @Dan: Thanks for forwarding my mail to the MySQL List! Regards, Jan From: Ashley Sheridan [mailto:a...@ashleysheridan.co.uk] Sent: Wednesday, June 16, 2010 3:09 AM To: Jan Reiter Cc: php-general@lists.php.net Subject: Re: [PHP] SQL Syntax 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 -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
