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

Reply via email to