SQL Join and Manipulate

Question

I have TABLE1 and TABLE2 as below:

TABLE1

STUDENT	DAY	SCORE
1	7	0
1	9	10
2	3	10
2	5	3
3	9	10

TABLE2

STUDENT	DAY	TEST
1	7	1
1	8	2
1	9	7
2	5	5
3	8	9
4	4	10

I need a query to JOIN TABLE2 to TABLE1 and then create column SCOREVSTEST which goes like this:

if SCORE >= TEST then SCOREVSTEST = 'SCORE >= TEST'
if SCORE < TEST then SCOREVSTEST = 'SCORE < TEST'

This my desirable output

STUDENT	DAY	SCORE	TEST	SCOREVSTEST
1	7	0	1	SCORE < TEST
1	9	10	7	SCORE >= TEST
2	5	3	5	SCORE < TEST

This is what I have so far:

SELECT * 
FROM TABLE1
LEFT JOIN TABLE2
    ON TABLE1.STUDENT = TABLE2.STUDENT
    AND TABLE1.DAY = TABLE2.DAY
CASE WHEN SCORE >= TEST THEN 'SCORE>= TEST'
ELSE 'SCORE<TEST' END AS SCOREVSTEST

Answer 1

You have the right idea, but this case expression should be in the select list, not part of or after the join:

SELECT    table1.student, table1.day, score, test,
          CASE WHEN score >= test THEN 'SCORE >= TEST' ELSE 'SCORE < TEST' END 
            AS scorevtest
FROM      table1
LEFT JOIN table2 ON ON table1.student = table2.student AND table1.day = table2.day