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
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