June 17, 2015 at 12:57 am
Hi All,
Please consider the below script
DROP TABLE #TA
DROP TABLE #TB
CREATE TABLE #TA (Id int,CheckValue nvarchar(10),Status int)
INSERT INTO #TA VALUES(123,'60',1)
INSERT INTO #TA VALUES(123,'45',0)
INSERT INTO #TA VALUES(123,'76',0)
INSERT INTO #TA VALUES(123,'50',0)
CREATE TABLE #TB (Id int,OCheckValue nvarchar(10),CheckValue nvarchar(10),OValue int)
INSERT INTO #TB VALUES(123,'45','60',90)
INSERT INTO #TB VALUES(123,'50','60',90)
INSERT INTO #TB VALUES(123,'76','60',90)
SELECT * FROM #TA
SELECT * FROM #TB
Now i want the result as
123 45 0 90
123 50 0 90
123 76 0 90
I am using the below query to get the result
SELECT A.*,B.OValue
FROM #TA A
LEFT JOIN #TB B ON A.Id=B.Id
AND A.CheckValue = CASEWHEN A.Status=1 THEN B.CheckValue
WHEN A.Status=0 THEN B.OCheckValue
END
But for 1 value i am getting 3 records. Any help on this.
June 17, 2015 at 1:20 am
Simple enough: in table A, you have a row containing CheckValue = 60 which matches three rows in table B, hence three results.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
June 17, 2015 at 1:20 pm
By the way - what you're seeing has nothing to do with LEFT join per se. You'll get the same results if you made this a "full join" (in this specific example).
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
June 18, 2015 at 9:38 am
Let's try this:
DECLARE @TA AS TABLE (Id int,CheckValue nvarchar(10),[Status] int)
INSERT INTO @TA VALUES(123,'60',1)
INSERT INTO @TA VALUES(123,'45',0)
INSERT INTO @TA VALUES(123,'76',0)
INSERT INTO @TA VALUES(123,'50',0)
DECLARE @TB AS TABLE (Id int,OCheckValue nvarchar(10),CheckValue nvarchar(10),OValue int)
INSERT INTO @TB VALUES(123,'45','60',90)
INSERT INTO @TB VALUES(123,'50','60',90)
INSERT INTO @TB VALUES(123,'76','60',90);
SELECT A.Id, A.CheckValue, A.[Status], MIN(B.OValue) AS OValue
FROM @TA AS A
LEFT JOIN @TB AS B
ON A.Id=B.Id
AND A.CheckValue =
CASE A.[Status]
WHEN 1 THEN B.CheckValue
WHEN 0 THEN B.OCheckValue
END
GROUP BY A.Id, A.CheckValue, A.[Status]
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply