May 26, 2010 at 4:11 am
Hi All,
I'm joining two tables to get the id name from another table the structure is
CREATE TABLE #tmp ( ID INT PRIMARY KEY identity (1,1), IdName VARCHAR(10))
CREATE TABLE #tmpTran (ID INT , VALUE1 INT)
INSERT INTO #tmp VALUES('id1')
INSERT INTO #tmp VALUES('id2')
INSERT INTO #tmp VALUES('id3')
INSERT INTO #tmpTran VALUES(1,10)
INSERT INTO #tmpTran VALUES(-1,10)
INSERT INTO #tmpTran VALUES(2,10)
INSERT INTO #tmpTran VALUES(3,10)
SELECT TT.ID, T.IdName , VALUE1 FROM #tmpTran TT INNER JOIN #TMP T
ON T.ID =TT.ID
here for the id -1 i want to give the value 'No Value' as a text. I can't include this at the table level. only in reports i need it.
I tried like
SELECT TT.ID, CASE TT.ID WHEN -1 THEN 'NOVALUE' ELSE T.IdName END IDNAME ,VALUE1 FROM #tmpTran TT , #TMP T
where T.ID =TT.ID
but not getting the 'no value' for -1.
how to achieve?
Thanks,
Regards,
Ami.
May 26, 2010 at 4:25 am
SELECT TT.ID,
CASE WHEN TT.ID = -1 THEN 'NOVALUE' ELSE T.IdName END IDNAME ,
VALUE1
FROM #tmpTran TT
LEFT OUTER JOIN #TMP T ON T.id = TT.id
May 26, 2010 at 4:26 am
Anamika, excellent work done in posting the necessary tables , sample data and an equally good "desired result".. Kudos to you!
Now for your desired result, try this query:
SELECT TT.ID,
CASE WHEN T.IdName IS NULL THEN 'NO VALUE'
ELSE CAST(TT.VALUE1 AS VARCHAR)
END [VALUE]
FROM #tmpTran TT LEFT JOIN #TMP T
ON T.ID =TT.ID
Tell us if that solved your issue! 🙂
May 26, 2010 at 4:36 am
You gotta use left outer join
SELECT TT.ID, CASE TT.ID WHEN -1 THEN 'NOVALUE' ELSE T.IdName END IDNAME ,VALUE1
FROM #tmpTran TT
left outer join #TMP T
on T.ID = TT.ID
- arjun
https://sqlroadie.com/
May 26, 2010 at 4:39 am
🙂 Didn't see the other responses. Good to find multiple people responding so fast though.
- arjun
https://sqlroadie.com/
May 26, 2010 at 5:13 am
Hi All,
First thanks for a quicker response.
the left outer join solves my purpose.
thanks a lot
Regards,
Ami
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply