Solved

sql join

Posted on 2015-01-26
14
191 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 48

Expert Comment

by:Vitor Montalvão
ID: 40572202
What kind of values is expected to be found in locationList?
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

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

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 48

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 48

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:
Scott Pletcher 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

Active Directory Webinar

We all know we need to protect and secure our privileges, but where to start? Join Experts Exchange and ManageEngine on Tuesday, April 11, 2017 10:00 AM PDT to learn how to track and secure privileged users in Active Directory.

Question has a verified solution.

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

Suggested Solutions

Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

808 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