Click here to Skip to main content
15,904,494 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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 
	      charIndex('\' , substring(Path,LEN(@RootLength)+1,LEN(@RootLength)))
		as FirstBusinessId,

	TRIM('\' from
	      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
Updated 12-Apr-24 9:38am

1 solution

I also asked this on SO and someone just answered the question over there.
I can't believe someone read my entire post and actually gave me the exact solution I was looking for. It's quite instructive. You may want to check it out.[^]

Also, check out this DB fiddle[^] where the person who answered the question created all the "live" data and added the final query so you can see all the tables and data in action.

Truly amazing that I was able to collaborate and get a solution.
Share this answer
PIEBALDconsult 12-Apr-24 17:23pm    
Please don't try to answer your own question. Use the Improve question button to add detail and context.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900