Re: [PHP] SQL Syntax
[ 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'. snip I appreciate your thoughts on this. My first thought is that you're going to endup with some very inefficient queries or come unstuck with that table schema the first time you have an attributes of different types. What happens if attribute 1 is dateTaken has the type date, attribute 2 is authorName with the type string and attribute 3 is an aspect ratio N:n? My second thought is to make sure you have a unique index on (pid,aid) in table b. Sticking to the question you asked. Lets assume the search for this run of the search query is owned by userId 35 and two attribute clauses: has attribute 1 50 and attribute 3 = 4 I'd use: drop temporary table if exists AttSearchMatches; select pid as targetPid, count(*) as criteraMatched from B where userId=35 and ( (b.aid=1 and b.value 50) OR (b.aid=3 and b.value =4) ) group by pid having criteraMatched = 2; drop temporary table if exists AttSearchMatches; select fields you want from criteraMatched cm on cm. inner join A on a.pid=criteraMatched.pid; drop temporary table AttSearchMatches; For best performance specify the temp table structure explicitly and add an index to pid. You could do this with a single query containing a sub-query rather than temporary tables but I've been bitten by sub-query performance before. Hope that helps, Nigel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: [PHP] SQL Syntax
On Wed, 2010-06-16 at 08:59 +0100, Nigel Wood wrote: I'd use: drop temporary table if exists AttSearchMatches; select pid as targetPid, count(*) as criteraMatched from B where userId=35 and ( (b.aid=1 and b.value 50) OR (b.aid=3 and b.value =4) ) group by pid having criteraMatched = 2; drop temporary table if exists AttSearchMatches; select fields you want from criteraMatched cm on cm. inner join A on a.pid=criteraMatched.pid; drop temporary table AttSearchMatches; Nope :-) Without the silly errors I'd use: drop temporary table if exists AttSearchMatches; select pid, count(*) as criteraMatched from B where b.userId=35 and ( (b.aid=1 and b.value 50) OR (b.aid=3 and b.value =4) ) group by b.pid having criteraMatched = 2; select a.fields you want from AttSearchMatches asm inner join A on a.pid=asm.pid; drop temporary table AttSearchMatches; Sorry, Nigel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: [PHP] SQL Syntax
Hi! Daniel Brown wrote: [Top-post.] You'll probably have much better luck on the MySQL General list. CC'ed on this email. On Tue, Jun 15, 2010 at 20:58, Jan Reiter the-fal...@gmx.net wrote: Hi folks! [[...]] 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'. [[...]] You need to do a multi-table join, table A joined to one instance of table B for each attribute relevant to your search. Roughly, syntax not tested, it is something like SELECT a.uid, a.pid FROM a JOIN b AS b1 ON a.pid=b1.pid JOIN b AS b2 ON a.pid=b2.pid JOIN ... WHERE b1.aid = 1 AND b1.value 100 AND b2.aid = 3 AND b2.value = 5 AND ... (assuming 'jpg' is coded as 5, what I take from your text). Now, I see some difficulties with this: 1) You are using the value column for anything, that may cause data type problems. 2) AFAIR, there was a post recently claiming the alias names (b1, b2, ...) could not be used in WHERE conditions, and the recommendation was to replace WHERE by HAVING. 3) If you need to support many attributes in one search, the number of tables joined grows, and the amount of data to handle (cartesian product!) will explode. What works fine with 3 criteria on 10 pictures (10 * 10 * 10 = 1000) may totally fail with 4 criteria on 200 pictures (200**4 = 800.000.000 = 800 million) 4) The more different attributes you store per picture, the larger your table B will become, and this will make the data grow for each join step. If you store 4 attributes each for 200 pictures, table B will already have 800 entries. In itself, that isn't much, but now the 4-way join will produce a cartesian product of 800**4 = 8**4 * 100**4 = 4096 * 100.000.000 = 409.600.000.000 combinations. In your place, I would use a separate table for attributes which are expected to be defined for all pictures, like size and image type. Then your general attributes table B will hold much fewer rows, thus each join step will profit. 5) Because of that explosion, it may be better to work with a temporary table, joining it to B for one attribute and thus reducing the data, then looping over such a step for all the relevant attributes. Good luck in experimenting! Jörg -- Joerg Bruehe, MySQL Build Team, joerg.bru...@sun.com Sun Microsystems GmbH, Komturstrasse 18a, D-12099 Berlin Geschaeftsfuehrer: Juergen Kunz Amtsgericht Muenchen: HRB161028 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: [PHP] SQL Syntax
[Top-post.] You'll probably have much better luck on the MySQL General list. CC'ed on this email. On Tue, Jun 15, 2010 at 20:58, Jan Reiter the-fal...@gmx.net 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 -- /Daniel P. Brown daniel.br...@parasane.net || danbr...@php.net http://www.parasane.net/ || http://www.pilotpig.net/ We now offer SAME-DAY SETUP on a new line of servers! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
