Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

sql join

Posted on 2015-01-26
14
Medium Priority
?
195 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 34

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 52

Expert Comment

by:Vitor Montalvão
ID: 40572202
What kind of values is expected to be found in locationList?
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 49

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 49

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

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 52

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 52

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 70

Accepted Solution

by:
Scott Pletcher earned 2000 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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

972 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