Solved

sql join

Posted on 2015-01-26
14
183 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 46

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
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

 
LVL 46

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 46

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 46

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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

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.​
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
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…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

896 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

14 Experts available now in Live!

Get 1:1 Help Now