Re: SQL Syntax

Am 16.06.2010 02:58, schrieb Jan Reiter:

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 Hi Jan,

i think the problem here is the architecture of your database and not the sql queries. Sure, there are a whay to solve this, but i think it is better (for the software maintainment and further work in your code), that you take 5 minutes and redesign your tables. In my opinion, your attribute,value pair table, should be a extern table with relation to table b. As for know, i dont know what you want to do, but i think, when the user have more than one picture with more attributes each picture, your query will be slow. By increasing the attributes count, you will show, the queries and the maintainment of code will be hard. I have some links here but i am sure, you will find more in google ;-) Regards Carlos Links: http://dev.mysql.com/tech-resources/articles/intro-to-normalization.html http://en.wikipedia.org/wiki/Entity-attribute-value_model http://weblogs.sqlteam.com/davidm/articles/12117.aspx