sql join

I am trying to join table on 2 columns one of the them is the list

Select * from Session S
left outer join Location L on (L.locationID=S.locationID OR L.locationID in (S.locationList)

if this approach is correct. I did not design the datebase that way
LVL 19
erikTsomikSystem Architect, CF programmer Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Scott PletcherConnect With a Mentor Senior DBACommented:
The most efficient method to do these types of comparisons is to split the list into a table of values, then do a lookup on that list.

DelimitedSplit8K is an extremely efficient splitter, so I'll use that.  The code to create it is below the query code.


Select s.*, l.*
from Session S
left outer join Location L on
    L.locationID = S.locationID OR
    Exists(select 1 from dbo.DelimitedSplit8K(S.locationList, ',') ds
               Where CAST(L.locationID AS varchar(10)) = ds.Item)
               --the CAST prevents errors if the list gets a nonnumeric value in it;
               --but it will cause an issue if the list of values has any leading zeros
               --so you make the choice of whether to CAST or not in the comparison


SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[DelimitedSplit8K]
--===== Define I/O parameters
        (@pString VARCHAR(8000), @pDelimiter CHAR(1))
--NOTE: (max) data type will at least double the time to do the split!
RETURNS TABLE WITH SCHEMABINDING AS
 RETURN
--===== "Inline" CTE Driven "Tally Table" produces values from 1 up to 10,000...
     -- enough to cover VARCHAR(8000)
  WITH E1(N) AS (
                 SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
                 SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
                 SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
                ),                          --10E+1 or 10 rows
       E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
       E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max
 cteTally(N) AS (--==== This provides the "base" CTE and limits the number of rows right up front
                     -- for both a performance gain and prevention of accidental "overruns"
                 SELECT TOP (ISNULL(DATALENGTH(@pString),0)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
                ),
cteStart(N1) AS (--==== This returns N+1 (starting position of each "element" just once for each delimiter)
                 SELECT 1 UNION ALL
                 SELECT t.N+1 FROM cteTally t WHERE SUBSTRING(@pString,t.N,1) = @pDelimiter
                ),
cteLen(N1,L1) AS(--==== Return start and length (for use in substring)
                 SELECT s.N1,
                        ISNULL(NULLIF(CHARINDEX(@pDelimiter,@pString,s.N1),0)-s.N1,8000)
                   FROM cteStart s
                )
--===== Do the actual split. The ISNULL/NULLIF combo handles the length for the final element when no delimiter is found.
 SELECT ItemNumber = ROW_NUMBER() OVER(ORDER BY l.N1),
        Item       = SUBSTRING(@pString, l.N1, l.L1)
   FROM cteLen l
;
GO
0
 
Pratima PharandeCommented:
This is correct.
But let us know what extact output you need so that it will make clear whether query is correct ot not
0
 
Mike EghtebasDatabase and Application DeveloperCommented:
The first one gives all from Session and what ever match exists in. Some L.locationIDs may show up null:
Select S.locationID As IDinSession, L.locationID As IDinLocation from Session S
left outer join Location L on L.locationID=S.locationID

OR

This one will show from both tables only if they match. This function the same as inner join below:
Select * from Session S
Where S.locationID IN (Select L.locationID From Location L)

Select S.locationID As IDinSession, L.locationID As IDinLocation from Session S
inner join Location L on L.locationID=S.locationID
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
Vitor MontalvãoMSSQL Senior EngineerCommented:
What kind of values is expected to be found in locationList?
0
 
PortletPaulfreelancerCommented:
I do not believe what you propose in the question is correct.
S.locationList is a field, not some "list of values" where each member of that list be separately joined to.

If S.locationList appears like this

123,456,789,2345

that is NOT 4 IDs, it is a single string of numerals and commas that looks like 4 IDs
Could you provide some data from both tables please?
0
 
erikTsomikSystem Architect, CF programmer Author Commented:
locationList is a string of locationsID separated by comma. The question is if I want to pull records like this it will take too much time . IS there any elegant way to do so using sql query
0
 
PortletPaulfreelancerCommented:
There are ways to do it, but I would describe none of those as elegant.
0
 
erikTsomikSystem Architect, CF programmer Author Commented:
Also can I say If locationid is contain some value do not check locationlist Inside the join
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
But why do you have a LocationID and a LocationList fields?
Being a relational database system the LocationList should be transformed in a table. Would be more easy to make the joins after.
0
 
erikTsomikSystem Architect, CF programmer Author Commented:
This is the database structure that I have inherited from other guy
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
By now you should already know that the query you posted don't work.
One of the solutions is to pass the data in the LocationList to a table so you can make the join.
0
 
erikTsomikSystem Architect, CF programmer Author Commented:
can I do some thing like this in the where clause
AND (S.locationKey in (<cfqueryparam cfsqltype="cf_sql_integer" value="#locHubLocations#" list="true">) OR
                              S.locationKeyList like '%#locHubLocations#%'
                        )
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Sorry, I don't even know what's that. For sure isn't T-SQL.
0
 
erikTsomikSystem Architect, CF programmer Author Commented:
Well just disregard querypam it's coldfuison. Can I say that key list column that is a table column is is in any values of the passed list.lochublocations is the list of locations
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.