Invalid Column Name on CONCAT_WS Sorry if this seems like rambling I'm not typically a DB person just trying to work through an issue.
I have two tables cases and legal_entity the data on the two tables looks like this
legal_entity




first_name
last_name
address_1_zip




Maria
Munoz
33122


John
Glenn
10003


Melissa
Johnson
90210




cases




style
case_number
create_date




Munoz, Maria
SL12345
2021-03-01 11:45:26.657


Glenn, John
SL45678
2021-03-02 12:10:14.552


Johnson, Melissa (DNY)
SG42561
2021-04-10 01:06:10.214




What I am trying to do is combine the last_name and first_name fields from the legal_entity table with a comma between them, and match (contains) it against the style field in the cases table. For everything in the last month basically. This is the query I'm using
SELECT
    first_name, last_name, address_1_zip,
    CONCAT_WS(',', last_name,first_name) AS CombinedName
FROM
    CPSQL.dbo.legal_entity AS LE
INNER JOIN CPSQL.dbo.cases as C ON LE.CombinedName=C.style
WHERE C.create_date &gt;= Convert(datetime, '2021-03-01') AND C.create_date &lt;= Convert(datetime, '2021-04-16')
ORDER BY C.create_date

The issue I run into is that it seems to not like the LE.CombinedName so I'm not sure how to reference the concatenated string against the style column in the cases table.
Any help would be greatly appreciated.
You can use only columns from the joined tables or in your case concatenated  version of it
SELECT
    first_name, last_name, address_1_zip,
    CONCAT_WS(',', last_name,first_name) AS CombinedName
FROM
    CPSQL.dbo.legal_entity AS LE
INNER JOIN CPSQL.dbo.cases as C ON CONCAT_WS(', ', LE.last_name,LE.first_name)=C.style
WHERE C.create_date &gt;= Convert(datetime, '2021-03-01') AND C.create_date &lt;= Convert(datetime, '2021-04-16')
ORDER BY C.create_date



CREATE TABLE legal_entity
    ([first_name] varchar(7), [last_name] varchar(7), [address_1_zip] int)
;
    
INSERT INTO legal_entity
    ([first_name], [last_name], [address_1_zip])
VALUES
    ('Maria', 'Munoz', 33122),
    ('John', 'Glenn', 10003),
    ('Melissa', 'Johnson', 90210)
;




CREATE TABLE cases
    ([style] varchar(22), [case_number] varchar(7), [create_date] varchar(23))
;
    
INSERT INTO cases
    ([style], [case_number], [create_date])
VALUES
    ('Munoz, Maria', 'SL12345', '2021-03-01 11:45:26.657'),
    ('Glenn, John', 'SL45678', '2021-03-02 12:10:14.552'),
    ('Johnson, Melissa (DNY)', 'SG42561', '2021-04-10 01:06:10.214')
;




SELECT
    first_name, last_name, address_1_zip,
    CONCAT_WS(',', last_name,first_name) AS CombinedName
FROM
    legal_entity AS LE
INNER JOIN cases as C ON CONCAT_WS(', ', LE.last_name,LE.first_name)=C.style
WHERE C.create_date &gt;= Convert(datetime, '2021-03-01') AND C.create_date &lt;= Convert(datetime, '2021-04-16')
ORDER BY C.create_date
GO


first_name | last_name | address_1_zip | CombinedName
:--------- | :-------- | ------------: | :-----------
Maria      | Munoz     |         33122 | Munoz,Maria 
John       | Glenn     |         10003 | Glenn,John  


db&lt;&gt;fiddle here


CREATE TABLE legal_entity
    ([first_name] varchar(7), [last_name] varchar(7), [address_1_zip] int)
;
    
INSERT INTO legal_entity
    ([first_name], [last_name], [address_1_zip])
VALUES
    ('Maria', 'Munoz', 33122),
    ('John', 'Glenn', 10003),
    ('Melissa', 'Johnson', 90210)
;
GO




CREATE TABLE cases
    ([style] varchar(22), [case_number] varchar(7), [create_date] varchar(23))
;
    
INSERT INTO cases
    ([style], [case_number], [create_date])
VALUES
    ('Munoz, Maria', 'SL12345', '2021-03-01 11:45:26.657'),
    ('Glenn, John', 'SL45678', '2021-03-02 12:10:14.552'),
    ('Johnson, Melissa (DNY)', 'SG42561', '2021-04-10 01:06:10.214')
;
GO




SELECT
    first_name, last_name, address_1_zip,
    CONCAT_WS(',', last_name,first_name) AS CombinedName
FROM
    legal_entity AS LE
INNER JOIN cases as C ON PATINDEX( CONCAT( LE.last_name,', ',LE.first_name,'%'),C.style) &gt; 0
WHERE C.create_date &gt;= Convert(datetime, '2021-03-01') AND C.create_date &lt;= Convert(datetime, '2021-04-16')
ORDER BY C.create_date
GO


first_name | last_name | address_1_zip | CombinedName   
:--------- | :-------- | ------------: | :--------------
Maria      | Munoz     |         33122 | Munoz,Maria    
John       | Glenn     |         10003 | Glenn,John     
Melissa    | Johnson   |         90210 | Johnson,Melissa


db&lt;&gt;fiddle here