Case, Joins And NULL Trouble

Hi

Consider two tables
id1 code1
----------- -----
1 a
2 b
3 c

id2 code2 value
----------- ----- -----------
1 a 0
2 a 1
3 b 1

They are joined on the code field.

For each code, I want the maximum corresponding value. If the value
doesn't exist (corresponding code in second table doesn't exist), I want
a NULL field returned.

The result should look like this:
code2 value
----- -----------
a 1
b 1
c NULL

I can't get it to include the NULL row.
While there are uniqe ID's in this example, the real life example uses a
horrible four field compound key.
Any help would be appreciated.

Ger.

The above example can be recreated by the following script.

DROP table #temp1
DROP table #temp2

SELECT 1 AS 'id1', 'a' AS 'code1'
INTO #temp1
UNION
SELECT 2, 'b'
UNION
SELECT 3, 'c'

SELECT 1 AS 'id2', 'a' AS 'code2', 0 AS value
INTO #temp2
UNION
SELECT 2, 'a', 1
UNION
SELECT 3, 'b', 1

SELECT code2, value
FROM #temp1 t1
LEFT JOIN #temp2 t2 ON t1.code1 = t2.code2
WHERE CASE
WHEN t2.value IS NULL THEN 1
WHEN t2.value = 0 THEN 2
WHEN t2.value = 1 THEN 3
END = (
SELECT
MAX( CASE
WHEN value IS NULL THEN 1
WHEN value = 0 THEN 2
WHEN value = 1 THEN 3
END )
FROM
#temp2
WHERE
code2 = t2.code2
)