Return value from another table that matches values from the first table

I have a simple problem. I have tables A and B, both contain 3 common columns (name, id, date), and besides those 3, both tables have completely different set of columns.

Now, I need to relate data from Table B to table A for visualization purposes. Since I am not that good with SQL, I thought I could ask if it’s possible, I know I can have computed columns as I already have one in table A for something simple. However I want to do something like this :

A new column in table A that contains a value of the column “Special” from table B for any row where name, date and ID from table A matches name, date and ID for table B, if no matches leave as blank or null.

Example

Table A:

Name | Date          | ID  | OtherData1     | OtherData2
-----+---------------+-----+----------------+-------------
John | March 2020    | SPC | Randomstuff    | randomstuff
Doe  | March 2020    | SPC | Randomstuff    | randomstuff
John | February 2020 | SPC | RandomStuff    | RandomStuff
Doe  | February 2020 | SPC | Randomstuff    | randomstuff

Table B:

Name | Date          | ID  | MoreData1     | SomeData2     | Special
-----+---------------+-----+---------------+---------------+--------
John | March 2020    | SPC | Randomstuff   | randomstuff   | True
Doe  | March 2020    | SPC | Randomstuff   | randomstuff   | False
John | February 2020 | SPC | RandomStuff   | RandomStuff   | True
Doe  | February 2020 | SPC | Randomstuff   | randomstuff   | True

Result in table A:

Name | Date          | ID  | OtherData1    |OtherData2   | SpecialImported
-----+---------------+-----+---------------+-------------+----------------
John | March 2020    | SPC | Randomstuff   | randomstuff | True
Doe  | March 2020    | SPC | Randomstuff   | randomstuff | False
John | February 2020 | SPC | RandomStuff   | RandomStuff | True
Doe  | February 2020 | SPC | Randomstuff   | randomstuff | True

Is this possible to do in SQL Server?

Answer

Yes, it is possible. But in order to reference another table in a computed column, you have to do it via creating a user-defined function. This will have name,date,id as input parameters and will output a bit if such a combination exists in table B. Something like:

CREATE FUNCTION [dbo].[f_check_tableB_tuple](@name nvarchar(max),@date datetime,@ID nvarchar(max))

RETURNS bit
as
begin
declare @result bit
select @result=Special from tableB where name=@name and date=@date and ID=@ID

RETURN @result

end

Then, you can use this function in a computed column:

alter table tableA add SpecialImported as (dbo.f_check_tableB_tuple(name,date,ID))