JOIN tables - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

JOIN tables

Author

Topic

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2015-04-05 : 15:47:24

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

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2015-04-05 : 17:42:58

Tried;

ISNULL(     
           CASE WHEN LEFT (TableB.EmisCode,7) = 'EMISATT' THEN 'EMISATT'  ELSE TableB.EmisCode, N'(novalue)')
           = ISNULL(TableA.Emiscode, N'(novalue)')

I receive the error- Incorrect syntax near ','.

Please Help

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2015-04-06 : 03:36:41

[code]declare @TableA table (MedicalCodeID int, EmisCode varchar(20), ReadCode varchar(20));
insert @tableA values(1, 'EMISATT', NULL), (2, NULL, 'PCSDT'), (3, 'TFHG', 'Hgi'), (4, 'YUGH', NULL);

declare @TableB table (DiaryID int, EmisCode varchar(20), ReadCode varchar(20));
insert @tableB values(1, 'EMISATT_AB', NULL), (2, 'EMISATT_C', NULL), (3, 'TFHG', 'Hgi'), (4, 'YUGH', NULL);

-- SwePeso
SELECT b.DiaryID,
a.MedicalCodeID
FROM @TableA AS a
INNER JOIN @TableB AS b ON b.EmisCode LIKE a.EmisCode + '%';[/code]

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources