RE: [PHP] SQL Syntax

On Wed, 2010-06-16 at 03:23 +0200, Jan Reiter wrote: > 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:ash@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 > > > I think maybe your table structure could do with a little work, as it doesn't lend itself to simple queries. It could probably be done the way you need with sub-queries, but as the tables become more populated and more people are triggering the queries, this is going to become very slow. Thanks, Ash http://www.ashleysheridan.co.uk