Get two values ​from the same table

Question

I have a table called Register

Id, Type_status, Status, Description

Example:

ID      Type_status   Status    Description
1       8000          8017      test

And i have a table called History with the following structure

Id, Id_Register, History_status, Payload

Example

Id      Id_Register   History_status    Payload
1       1             8015              {"name": "test"}
2       1             8016              {"token": "example"} 
3       1             8017              {"email": "test@test.com"}

What I need is to be able to execute a query that will bring me the value of Record_Id and Payload value from the History table when the Id_Register = 1 and the History_status is 8015 and 8017 with History_status 8017 is email = test@test.com

The expected result is

Id_Register      Name    Token
1                test.   example   

I need to get all the names and token of the records that in the 8017 state have as email test@test.com

Mention that I have already solved the comparison of the text in the json, what I could not achieve is the query to obtain the two values for the same table depending on the states that I mention.

JSON_VALUE(cast(Payload as nvarchar(max)), '$.email') LIKE '%test@test.com%'

Answer 1

You just need to join twice the table History with the table Register. The first join will get you the names, and the second the tokens. To differentiate each one you use aliases.

select R.Id, 
       json_value(N.Payload, '$.Name') as Name, 
       json_value(T.Payload, '$.Token') as Token
from Register as R
     left join History as N on N.Id_Register = R.Id and 
                               substring(N.Payload, 1, 8) = '{"Name":'
     left join History as T on T.Id_Register = R.Id and 
                               substring(T.Payload, 1, 9) = '{"Token":'