Query to pull data from three tables into a final temp table?

Question

I'm wondering:

1. how to use only SQL (T-SQL for SQL Server)
2. to query 3 tables
3. and insert the result data (shown below) into a new temp table.

Temp Table Result

The resulting temp table will contain data (columns) from all three tables and look like the following:

(Note Path is shortened to fit screen)

FileId    Path           PartnerKey   Field1   Field2  Field3
   1     106\10149\PROD     24          DDD      GGG    Alt2

• FileId & Path are from Table1
• PartnerKey is from Table2
• Field1, Field2, & Field3 are from Table3

Note: I show all 3 Source tables with sample data below.

The Challenge

I need data that is parsed out of a column in the first table, so I can query the 2nd and 3rd table.

Here's what I mean.
Every value in the first table column will have the following format:

1. \\companyName\Production\Storage\Data\Connection\106\10149\PROD\
2. \\companyName\Production\Storage\Data\Connection\106\NotAnId\PROD\

• The first value 106 in both examples above will always be numeric representing a BusinessId.
• However, the 2nd value 10149 and NotAnId will sometimes be a BusinessId and sometimes just be part of the path (non-numeric).

I need to parse out those two values and use them to look up a row in the 2nd Table.

1. When the 2nd parsed out value is a valid integer then use it to query the 2nd Table.
2. However, if the 2nd parsed value is not a valid integer then use the first parsed out value (guaranteed to always be a valid integer) to query the 2nd Table.

Query 2nd Table For Region & Type

1. When we have the BusinessId from parsing
2. we will query the 2nd Table where Region is North
3. and Type is World to get the PartnerKey.

Use PartnerKey to Query 3rd Table

The PartnerKey will then be used to query the 3rd Table to get Field1, Field2 & Field3.

All of the resultant data will be inserted into the temp table shown above.

Table 1 PathInfo

FileId     Path
    1    \\companyName\Production\Storage\Data\Connection\106\10149\PROD\
    2    \\companyName\Production\Storage\Data\Connection\1723\3763\PROD\
    3    \\companyName\Production\Storage\Data\Connection\1534\1216\PROD\
    4    \\companyName\Production\Storage\Data\Connection\1534\NotAnId\PROD\
    5    \\companyName\Production\Storage\Data\Connection\1534\OtherPath\PROD\

Table 2 Region

ID  BusinessId  Region  Type    PartnerKey
24  106         NORTH   NATIONAL    23
24  24          EAST    WORLD       23
25  10149       NORTH   NATIONAL    24
26  26          NORTH   NATIONAL    25
27  27          SOUTH   NATIONAL    26
29  29          NORTH   WORLD       28
30  30          EAST    WORLD       29

Table 3 - PartnerInfo

PartnerKey   Field1    Field2  Field3
23            AAA       BBB     Alt1
24            DDD       GGG     Alt2
25            XXX       ZZZ     Alt2

What Have I Tried?

I have created and run the following query which maps each FileId and Path to a FirstBusinessId and SecondBusinessId (when it is a valid integer).

DECLARE @RootLength VARCHAR(100) = '\\companyName\Production\Storage\Data\Connection\'
  
  select FileId, Path,
    TRIM('\' from 
      substring(Path,LEN(@RootLength)+1,
          charIndex('\' , substring(Path,LEN(@RootLength)+1,LEN(@RootLength)))
          )) 
        as FirstBusinessId,

    substring(
    TRIM('\' from
    substring(Path,LEN(@RootLength)+LEN(substring(Path,LEN(@RootLength)+1,
          charIndex('\' , substring(Path,LEN(@RootLength)+1,LEN(@RootLength))
          )) ),LEN(@RootLength))), 0, charIndex('\',
          TRIM ('\' from substring(Path,LEN(@RootLength)+LEN(substring(Path,LEN(@RootLength)+1,
          charIndex('\' , substring(Path,LEN(@RootLength)+1,LEN(@RootLength))
          )) ),LEN(@RootLength)))
          ))
        as SecondBusinessId
 into #BusinessIDTemp -- temp table target
  from PathInfo

Answer 1

SELECT b.FileId, b.path, region.PartnerKey, p.Field1, p.Field2, p.Field3
  FROM #BusinessIDTemp b
INNER JOIN Region ON 
       ( TRY_CONVERT(int, SecondBusinessID) IS NOT NULL AND  SecondBusinessID=Region.BusinessID)
        OR
       ( TRY_CONVERT(int, SecondBusinessID) IS NULL AND  FirstBusinessID=Region.BusinessID)

INNER JOIN PartnerInfo p ON
       REGION.PartnerKey = P.PartnerKey

fiddle

FileId	path	PartnerKey	Field1	Field2	Field3
1	\companyName\Production\Storage\Data\Connection\106\10149\PROD| 24	DDD	GGG	Alt2