Post reply

If no record (from table variable) found in a table return null value instead

  • September 29, 2020 at 5:04 pm

    Go to Answer

    #3791723

    Hello,

    I am sure this is simple but I just can't get what I am looking for. Hopefully someone can help a novice.

    Let me explain:

    I am trying to check a table variable value (list of accounts) against a table and return a full listing of data, but return NULL where the account does not exist in the table.

    Take the following as a simple example of what the data table looks like:

    CREATE TABLE dbo.TestTable
    (
    [week]INT
    ,[account]VARCHAR(4)
    ,[value]INT
    )

    INSERT INTO dbo.TestTable ([week],[account],[value])
    VALUES ('1','A001','134')
       ,('1','A002','23')
       ,('1','A003','67')
       ,('1','A004','566')
       ,('1','A005','3325')

    And the following list of accounts held in a table variable:

    DECLARE @tv_AccList TABLE ([account] VARCHAR(7))
    INSERT INTO @tv_AccList
    VALUES ('A001')
       ,('A002')
       ,('A003')
       ,('A004')
       ,('A005')
       ,('A006')
       ,('A007')

    There are no records that exist in the TestTable for the 2 accounts (A006 and A007) from the table variable (@tv_AccList).

    So the result set i am looking for should look like the following for example:

    resultset

    Please can someone help?

  • September 29, 2020 at 6:54 pm

    #3791744

    What do you want to happen if there are accounts in TestTable with no matches in @tv_AccList? That scenario is not covered by your sample data.

     

    Cheers!

  • September 29, 2020 at 7:04 pm

    Answer

    #3791747

    You just need a LEFT OUTER JOIN:

     Select [week] = coalesce(tt.[week], 1)
          , al.Account
          , tt.[value]
       From @tv_AccList             al
       Left Join dbo.TestTable      tt On tt.Account = al.Account;

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply