JOIN Tables

[dr223]

Hi,

I have 2 tables TableA and TableB

TableA

MedicalCodeID    EmisCode     ReadCode    
1                EMISATT      NULL          
2                NULL         PCSDT         
3                TFHG         Hgi           
4                YUGH         NULL

TableB

DiaryID          EmisCode     ReadCode    
1                EMISATT_AB     NULL        
2                EMISATT_C      NULL        
3                TFHG           Hgi         
4                YUGH           NULL

Results I want to receive;

DiaryID          MedicalCodeID 
1                1                   
2                1      
3                3           
4                4

Therefore, the idea is if the emiscode = EMISATT% on TableB take medicalcodeID = 1, otherwise, take the exact match.

Code of the JOIN I have now is as shown (this code takes the exact match of the emiscode) but I want to update it to obtain the above results - where it will pull the EMISATT if the first 7 characters start with EMISATT, otherwise do exact match

ISNULL(TableB.EmisCode, N'(novalue)') = ISNULL(TableA.Emiscode, N'(novalue)')

Thank you so much

[DataMiser]

I am confused you say the code of the Join is as shown but you have not shown any join code.

[dr223]

Sorry My mistake ..

What I meant was the comparison between the two fields, and that should yield the results as shown above..

Thanks