What kind of SQL query can I do to pull data from three tables into a final temp table?

See more:

SQL

Anyone want to take this one on? I promise it's not my homework. 🤓

I'm wondering:
1. how to use only SQL (T-SQL for Microsoft 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 field 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 I have tried:

I've parsed out the first values (BusinessIds) and inserted into a first temp table with the following (really ugly) query:

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

Results in data like:

FirstBusinessID	SecondBusinessID
     106	       11528
     106	       5492
     106	         7620
     106	        7674
     47	        MAIN
     21626     	MAIN
     21731   	MAIN
     21855   	MAIN
     74	        MAIN