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:
Please can someone help?
September 29, 2020 at 6:54 pm
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!
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 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy