JC <[EMAIL PROTECTED]> wrote on 02/01/2006 01:15:00 PM:
> Hi all,
>
> I really need your help.
>
> I have two tables:
>
> table1:
> id1|1|2|3|4|
> 000+a+b+c+d
> 001+e+f+g+h
> .
> .
> .
> and
>
> table2:
> id2|col1|col2|col3
>1+val1+val2+val3
>2+val4+val5+val6
>3
>4
>.
>.
>.
>
> columns (1,2,3,4,...) in table1 are rows (id2) in table2. I want to
query
> rows in table2 such that id2 IN (all columns in table1 except first
> columns).
>
> Is this possible to do in one statement? I know how to do this in
> multiple queries, just wonder anyone knows how to optimize this.
>
> Thanks,
> JC
> --
>
I am afraid you can't write that kind of query in SQL. The syntax of the
language just doesn't allow one value to be compared across two or more
columns without some kind of major hack or a bunch of typing (something
like "val=col1 and val=col2 and ... and val=colN"). May I suggest that you
redesign table1 so that it looks like this:
id1|t2_id|value
Sure you end up with more rows but what you gain in flexibility should
more than make up for the pittance of space you will need to store a bunch
of extra row pointers. There is a relatively simple query pattern you can
use to convert this new "vertical" design back to your original
("pivoted") design
SELECT
id1,
SUM(if(td2_id) = 1, value,0) as 1,
SUM(if(td2_id) = 2, value,0) as 2,
SUM(if(td2_id) = 3, value,0) as 3,
...
SUM(if(td2_id) = N, value,0) as N
FROM table1
GROUP BY id1;
Perhaps if you described your situation more accurately, you could get a
better response. We on the list are used to dealing with some rather
complex designs and issues so don't feel at all like you need to "dumb it
down" for us. The very fact that you tried to simplify your design
actually made it harder to give you a decent answer.
Sorry!
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
