List:General Discussion« Previous MessageNext Message »
From:Joerg Bruehe Date:June 16 2010 12:49pm
Subject:Re: [PHP] SQL Syntax
View as plain text  
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-fallen@stripped> 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.Bruehe@stripped
Sun Microsystems GmbH,   Komturstrasse 18a,   D-12099 Berlin
Geschaeftsfuehrer: Juergen Kunz
Amtsgericht Muenchen: HRB161028

Thread
Re: [PHP] SQL SyntaxDaniel Brown16 Jun
  • Re: [PHP] SQL SyntaxJoerg Bruehe16 Jun
Re: [PHP] SQL SyntaxNigel Wood16 Jun
  • Re: [PHP] SQL SyntaxNigel Wood16 Jun