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