SQL left join case statement

Question

Need some help working out the SQL. Unfortunately the version of tsql is SybaseASE which I'm not too familiar with, in MS SQL I would use a windowed function like RANK() or ROW_NUMBER() in a subquery and join to those results ...

Here's what I'm trying to resolve

TABLE A  
Id    
1  
2  
3

TABLE B  
Id,Type  
1,A  
1,B  
1,C  
2,A  
2,B  
3,A  
3,C    
4,B
4,C  

I would like to return 1 row for each ID and if the ID has a type 'A' record that should display, if it has a different type then it doesn't matter but it cannot be null (can do some arbitrary ordering, like alpha to prioritize "other" return value types)

Results:
1, A
2, A
3, A
4, B

A regular left join (ON A.id = B.id and B.type = 'A') ALMOST returns what I am looking for however it returns null for the type when I want the 'next available' type.

Answer 1

You can use a INNER JOIN on a SubQuery (FirstTypeResult) that will return the minimum type per Id. Eg:

SELECT TABLEA.[Id], FirstTypeResult.[Type]
FROM TABLEA 
JOIN (
   SELECT [Id], Min([Type]) As [Type] 
   FROM TABLEB
   GROUP BY [Id]
) FirstTypeResult ON FirstTypeResult.[Id] = TABLEA.[Id]