Inner join

  • 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.

  • 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

  • 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! 🙂

  • 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/

  • 🙂 Didn't see the other responses. Good to find multiple people responding so fast though.

    - arjun

    https://sqlroadie.com/

  • 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