0

I am trying to calculate column based on other columns from many tables and then I have to order my results based on this column

Table users 

id_user, module1, module2, module3 ...
Ex
user1,   option1, option2, option2
user2,   option2, option1, option2
user3,   option1, option1, option1
user4,   option1, option1, option1

Tables module1, module2 .... have options from 1 to 20 so instead of

user1, option1, option2, option3 it may as well be user1, option15, option15, option15

Table module1

id1,     id2,     value
Ex
option1, option1, 10
option1, option2, 5
option1, option3, 8
option1, option4, 9
option2, option2, 10
option2, option3, 7
option2, option4, 2

Table module2

id1,     id2,     value
Ex
option1, option1, 8
option1, option2, 6
option1, option3, 7
option1, option4, 4
option2, option2, 9
option2, option3, 7
option2, option4, 1

Table module3

id1,     id2,     value
Ex
option1, option1, 9
option1, option2, 6
option1, option3, 5
option1, option4, 10
option2, option2, 9
option2, option3, 3
option2, option4, 7

What I need is to calculate for ex the total value for 

(user1,user2) = module1(1,2) + module2(2,1) + module3(2,2) 
              = module1(1,2) + module2(1,2) + module3(2,2)
              = 5 + 6 + 9 = 20

And then for user1 and all the other users and order by total value

Thank you, Monica

Moni
  • 1
  • 1
  • Why do you add `type3(2,2)` instead of `type3(1,2)`? – Barmar May 24 '15 at 11:49
  • Maybe don't have 3 different tables, and three different columns all holding essentially the same kind of thing? – Strawberry May 24 '15 at 12:01
  • Does the `data` table just have 2 rows, corresponding to the two ID columns in the other 3 tables? Or is this just a subset of the whole problem? I suspect you'll need to use dynamic SQL to handle the general case. – Barmar May 24 '15 at 12:03
  • If idd1 option for type3 is 2 and idd2 option for type3 is also 2 there is type3(2,2) – Moni May 25 '15 at 06:08
  • 1
    Have you looked at [normalization](http://stackoverflow.com/questions/246701/what-is-normalisation-or-normalization) to make your database structure easier to read? (And easier to query!) – KidCode May 25 '15 at 06:34

0 Answers0