April 5, 2015 at 2:09 pm
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
April 6, 2015 at 3:05 am
Quick solution, elementary case statement!
😎
Note: it took about 5 min to prepare the DDL and the consumable data, less than a minute to write the actual query!
/* In a safe place */
USE tempdb;
GO
SET NOCOUNT ON;
GO
/* Prepare sample schema DDL and consumable sample data set
Hint: do prepare this in the future as the likelyhood of
getting answers is a lot higher ;-)
*/
IF OBJECT_ID(N'dbo.TableA') IS NOT NULL DROP TABLE dbo.TableA;
CREATE TABLE dbo.TableA
(
MedicalCodeID INT NOT NULL
,EmisCode VARCHAR(50) NULL
,ReadCode VARCHAR(10) NULL
);
IF OBJECT_ID(N'dbo.TableB') IS NOT NULL DROP TABLE dbo.TableB;
CREATE TABLE dbo.TableB
(
DiaryID INT NOT NULL
,EmisCode VARCHAR(50) NULL
,ReadCode VARCHAR(10) NULL
);
INSERT INTO dbo.TableA
(
MedicalCodeID
,EmisCode
,ReadCode
)
VALUES
(1,'EMISATT',NULL )
,(2, NULL ,'PCSDT')
,(3,'TFHG' ,'Hgi' )
,(4,'YUGH' ,NULL );
INSERT INTO dbo.TableB
(
DiaryID
,EmisCode
,ReadCode
)
VALUES
(1,'EMISATT_AB',NULL )
,(2,'EMISATT_C ',NULL )
,(3,'TFHG' ,'Hgi' )
,(4,'YUGH' ,NULL );
/* Return 1 for dbo.TableB.EmisCode like 'EMISATT%'
else dbo.TableA.MedicalCodeID
*/
SELECT
TB.DiaryID
,CASE
WHEN TB.EmisCode LIKE 'EMISATT%' THEN 1
ELSE TA.MedicalCodeID
END AS MedicalCodeID
FROM dbo.TableB TB
LEFT OUTER JOIN dbo.TableA TA
ON TB.EmisCode = TA.EmisCode;
Results
DiaryID MedicalCodeID
----------- -------------
1 1
2 1
3 3
4 4
April 6, 2015 at 9:22 am
Or maybe you might need a LIKE in your JOIN clause.
SELECT
TB.DiaryID
,TA.MedicalCodeID
FROM dbo.TableA TA
JOIN dbo.TableB TB ON TB.EmisCode LIKE TA.EmisCode + '%';
April 6, 2015 at 11:22 am
Luis Cazares (4/6/2015)
Or maybe you might need a LIKE in your JOIN clause.
SELECT
TB.DiaryID
,TA.MedicalCodeID
FROM dbo.TableA TA
JOIN dbo.TableB TB ON TB.EmisCode LIKE TA.EmisCode + '%';
Neat solution but likely to be more costly on larger sets and less index friendly;-)
😎
April 6, 2015 at 12:03 pm
Eirikur Eiriksson (4/6/2015)
Luis Cazares (4/6/2015)
Or maybe you might need a LIKE in your JOIN clause.
SELECT
TB.DiaryID
,TA.MedicalCodeID
FROM dbo.TableA TA
JOIN dbo.TableB TB ON TB.EmisCode LIKE TA.EmisCode + '%';
Neat solution but likely to be more costly on larger sets and less index friendly;-)
😎
You're probably right, but the logic is different and adding a fifth row to TableB can cause different results.
IF OBJECT_ID(N'dbo.TableA') IS NOT NULL DROP TABLE dbo.TableA;
CREATE TABLE dbo.TableA
(
MedicalCodeID INT NOT NULL
,EmisCode VARCHAR(50) NULL
,ReadCode VARCHAR(10) NULL
);
CREATE CLUSTERED INDEX CIndexXA ON dbo.TableA(EmisCode);
IF OBJECT_ID(N'dbo.TableB') IS NOT NULL DROP TABLE dbo.TableB;
CREATE TABLE dbo.TableB
(
DiaryID INT NOT NULL
,EmisCode VARCHAR(50) NULL
,ReadCode VARCHAR(10) NULL
);
CREATE CLUSTERED INDEX CIndexXB ON dbo.TableB(EmisCode);
INSERT INTO dbo.TableA
(
MedicalCodeID
,EmisCode
,ReadCode
)
VALUES
(1,'EMISATT',NULL )
,(2, NULL ,'PCSDT')
,(3,'TFHG' ,'Hgi' )
,(4,'YUGH' ,NULL );
INSERT INTO dbo.TableB
(
DiaryID
,EmisCode
,ReadCode
)
VALUES
(1,'EMISATT_AB',NULL )
,(2,'EMISATT_C ',NULL )
,(3,'TFHG' ,'Hgi' )
,(4,'YUGH' ,NULL )
,(5,'YUGH_EO' ,NULL );
/* Return 1 for dbo.TableB.EmisCode like 'EMISATT%'
else dbo.TableA.MedicalCodeID
*/
SELECT
TB.DiaryID
,CASE
WHEN TB.EmisCode LIKE 'EMISATT%' THEN 1
ELSE TA.MedicalCodeID
END AS MedicalCodeID
FROM dbo.TableB TB
LEFT OUTER JOIN dbo.TableA TA
ON TB.EmisCode = TA.EmisCode;
SELECT
TB.DiaryID
,TA.MedicalCodeID
FROM dbo.TableA TA
JOIN dbo.TableB TB ON TB.EmisCode LIKE TA.EmisCode + '%';
April 6, 2015 at 12:50 pm
Luis Cazares (4/6/2015)
You're probably right, but the logic is different and adding a fifth row to TableB can cause different results.
Get what you are saying, thought that since "EMISATT" is not a column name the reference would be literal, hence the fifth row should return NULL.
😎
Changing the query to
SELECT
TB.DiaryID
,CASE
WHEN TB.EmisCode LIKE TA.EmisCode + '%' THEN 1
ELSE TA.MedicalCodeID
END AS MedicalCodeID
FROM dbo.TableB TB
LEFT OUTER JOIN dbo.TableA TA
ON TB.EmisCode = TA.EmisCode;
will still produce a better plan.
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply