How to coalesce multi table

[mutawari]

dear all,

i have 2 tables. first table named std_table :

|  ID_std  |  std_1  | std_2  |  std_3  |  std_4  |  std_5  |
|    115   |    A    |    B   |    A    |    C    |    D    |

then i gave another table named cpr_table :

|  ID_cpr  |  ID_std  |  cpr_1  | cpr_2  |  cpr_3  |  cpr_4  |  cpr_5  |
|    123   |    115   |    A    |    C   |    A    |    D    |    D    |

i need a query to compare those two tables to have some result like this :

|  ID_cpr  |  ID_std  |  cpr_1  | cpr_2  |  cpr_3  |  cpr_4  |  cpr_5  | VALUE |
|    123   |    115   |    A    |    C   |    A    |    D    |    D    |  7,5  |

Value gets 7,5 because std_1 = cpr_1 ( i give 2,5 points ), std_2 != cpr_2 (i give 0 points), std_3 = cpr_3 ( i give 2,5 points ), std_4 != cpr_4 ( i give 0 points ) and std_5 = cpr_5 ( i give 2,5 points ).

what would it be for the expression? i use case expression but it didn't work.

i hope i could explain better. any sugestion for my problem?

regards,

mutawari

[peter_budo]

You need to take it in steps

1. Get values out of database and store it in appropriate object
2. Make calculations
3. Present result in final table

[mutawari]

that's a great idea...
but peter, what kind of expression should i use for the first step?

best regards,

mutawari

[peter_budo]

Inner join for start

[mutawari]

peter,
would you give me some examples for the first step with my problem?
i my mind comes this way. i create table output like this

| cpr   | Values |
| cpr_1 |  2,5   |
| cpr_2 |   0    |
| cpr_3 |  2,5   |
| cpr_4 |   0    |
| cpr_5 |  2,5   |

is it true?
what is the expression? i'm getting confuse...

please help..

best regards,
mutawari