CODEWITHSUNDEEP
sql
user2676309
user2676309

Reputation: 1

How do I look up row values in another table's column?

I have a table A and Table B

Table A 

Student     Math    Science
1           65      38
2           72      99
3           83      85
4           95      91
5           49      20
6           60      80

Table B 

Course      score_low   score_high  Mark
Math        0           50          D
Math        51          80          C
Math        81          90          B
Math        91          100         A
Science     0           50          D
Science     51          80          C
Science     81          90          B
Science     91          100         A

What I want to see is Joining Table a with Table B

Student     Math    Science     Math Mark   Science Mark
1           65      38          C           D
2           72      99          C           A
3           83      85          B           B
4           95      91          A           A
5           49      20          D           D
6           60      80          C           C

Upvotes: 0

Views: 166

Answers (3)

Simon Elms
Simon Elms

Reputation: 19628

Try this:

select a.Student, a.Math, a.Science, bm.Mark as [Math Mark], bs.Mark as [Science Mark]
from TableA a 
    join TableB bm on a.Math between bm.score_low and bm.score_high
                and bm.Course = 'Math'
    join TableB bs on a.Science between bs.score_low and bs.score_high
                and bs.Course = 'Science'
order by a.Student;

This was written in T-SQL, as used on SQL Server. If your version of SQL doesn't have a between clause you can change the code to:

    join TableB bm on a.Math >= bm.score_low 
                and a.Math <= bm.score_high
                and bm.Course = 'Math'
    join TableB bs on a.Science >= bs.score_low
                and a.Science <= bs.score_high
                and bs.Course = 'Science'

However, I notice the grade ranges are the same for both Math and Science. Will these ranges change? If not, or at least not frequently, it'd be easier to use a case statement:

select a.Student, a.Math, a.Science, 
    case 
        when a.Math between 0 and 50 then 'D'
        when a.Math between 51 and 80 then 'C'
        when a.Math between 81 and 90 then 'B'
        when a.Math between 91 and 100 then 'A'
        else 'X'
    end as [Math Mark], 
    case 
        when a.Science between 0 and 50 then 'D'
        when a.Science between 51 and 80 then 'C'
        when a.Science between 81 and 90 then 'B'
        when a.Science between 91 and 100 then 'A'
        else 'X'
    end as [Science Mark]
from TableA a 
order by a.Student;

Upvotes: 0

Taryn
Taryn

Reputation: 247620

Part of your problem is that your tableA is denormalized and you have a separate column for Math and Science. One way to get the result would be to unpivot the data in tableA so you can easily join to tableb. Once you have joined the data based on the course name and the score range, then you can use an aggregate function with a CASE expression to get the final result in separate columns:

select a.student,
  max(case when a.Course = 'math' then a.mark end) Math,
  max(case when a.Course = 'science' then a.mark end) science,
  max(case when a.Course = 'math' then b.mark end) MathMark,
  max(case when a.Course = 'science' then b.mark end) ScienceMark
from
(
  select student, math mark, 'Math' Course 
  from tablea
  union all
  select student, Science mark, 'Science' Course 
  from tablea
) a
inner join tableb b
  on a.Course = b.Course
  and a.mark >= b.score_low
  and a.mark <= b.score_high
group by a.student;

See SQL Fiddle with Demo.

Or this could be written using multiple joins on tableb:

select a.student,
  a.math,
  a.science,
  bMath.mark mathMark,
  bSci.mark ScienceMark
from tablea a
left join tableb bMath
  on a.math >= bMath.score_low
  and a.math <= bMath.score_high
  and bMath.course = 'Math'
left join tableb bSci
  on a.science >= bSci.score_low
  and a.science <= bSci.score_high
  and bSci.course = 'Science';

See SQL Fiddle with Demo. Both will give a result:

| STUDENT | MATH | SCIENCE | MATHMARK | SCIENCEMARK |
-----------------------------------------------------
|       1 |   65 |      38 |        C |           D |
|       2 |   72 |      99 |        C |           A |
|       3 |   83 |      85 |        B |           B |
|       4 |   95 |      91 |        A |           A |
|       5 |   49 |      20 |        D |           D |
|       6 |   60 |      80 |        C |           C |

Upvotes: 5

lordkain
lordkain

Reputation: 3109

havent tried it yet, but something like this

select *, 
(select Mark from tableB where course='Math' and score_low >= a.Math and score_high <= a.Math), 

(select Mark from tableB where course='Science' and score_low >= a.Math and score_high <= a.Math) 
 from tableA a

Upvotes: 0

Related Questions