[
> 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
