Solved

sql join

Posted on 2015-01-26
14
179 Views
Last Modified: 2015-01-30
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
0
Comment
Question by:erikTsomik
  • 5
  • 4
  • 2
  • +3
14 Comments
 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 40572073
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
 
LVL 33

Expert Comment

by:Mike Eghtebas
ID: 40572081
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
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 40572202
What kind of values is expected to be found in locationList?
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40572207
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
 
LVL 19

Author Comment

by:erikTsomik
ID: 40572458
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
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40572468
There are ways to do it, but I would describe none of those as elegant.
0
 
LVL 19

Author Comment

by:erikTsomik
ID: 40572478
Also can I say If locationid is contain some value do not check locationlist Inside the join
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 40572519
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
 
LVL 19

Author Comment

by:erikTsomik
ID: 40572575
This is the database structure that I have inherited from other guy
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 40572681
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
 
LVL 19

Author Comment

by:erikTsomik
ID: 40572709
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
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 40572718
Sorry, I don't even know what's that. For sure isn't T-SQL.
0
 
LVL 19

Author Comment

by:erikTsomik
ID: 40572804
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
 
LVL 69

Accepted Solution

by:
ScottPletcher earned 500 total points
ID: 40573112
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

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now