Merge Data fields from different tables into one view table

1

Hello.

I'm looking to combine data from different tables to result in a Case Number.

Tbl.CaseRequest
col.RequestID = 1234
col.RequestYear = 2021
col.RequestRegion = N

Tbl.Subject
col.FirstName = Derrick
col.LastName = Johnson

The result of the view table should be:

vw.CaseNumberDisplay
col.RequestID = 1234
col.CaseNumberDisplay = "N-1234-Johnson-2021"

What is the best way to accomplish getting the CaseNumberDisplay? It is easier for the users to reference this field for reports.

Thank you

2

Please provide usable ddl and dml

Create table xyz
Insert into

Also how are these tables related which column? since we do not know if the columns are int, varchar etc, it would just be guess work

create view vw_CaseNumberDisplay
as
select concat(RequestRegion, '-', RequestID, '-', RequestYear)
  from