CODEWITHSUNDEEP
sqlsql-servert-sql
Nate Pet
Nate Pet

Reputation: 46222

SQL Need Inner Join

Please note that what I have below the system already has in place so changing it will take time. As such, I know the best way is to normalize but...

I have the followng 2 tables

Tbl1

CompId      CommaList
------      --------
2           '122','54','90'   
54          '53','76'
34          '87' 
22          '98'


Tbl2

ID     ClientId   
--     --------
1      122
2      76 
3      87
4      98

What I need is to join Tbl1 and Tbl2 and then return the CompId and ID

The join should be done from tbl2.ClientId with tbl1.CommaList (which is a comma delimted list from which I need to find a matching value from.

Hope this make sense.

    select tbl1.CompId, tbl2.Id
    from tbl1  join tbl2 on CommaList = tbl2.ClientId

Obviously this will not work as they are of different types and I need furthermore find the list within CommaList.

The final result should look like:

ID       CompId
--       ------
1        2 
2        54
3        34
4        22

Upvotes: 1

Views: 110

Answers (5)

Conrad Frix
Conrad Frix

Reputation: 52645

If for some reason you couldn't use the Char Index or like solutions you could split the comma list and then join. For example if you needed to do the conceptual equivalent of

from tbl2 on CommaList = tbl2.ClientId LEFT join tbl1 join 

with split as (

SELECT 
       CompID,
       Substring(commalist, number, Charindex(',', commalist + ',', number) 
                                    - number) 
                                                                   AS clientID 
FROM   tbl1  
       JOIN master..spt_values v 
         ON number <= Len(commalist) 
            AND Substring(',' + commalist, number, 1) = ',' 
WHERE  v.type = 'P' )

SELECT 

    split.CompId,
    split.clientID,
    tbl2.id


FROM 

  split
  LEFT JOIN tbl2 
  on tbl2.clientID = split.clientID

DEMO

Notes:

  • I didn't bother with dealing with the quote delimited part but you could do that easily by replace(commalist,'''','')
  • As is, this only works on values of commalist that are <= 2047 in length. If you're dealing with larger lists you can use GarethD's second example in this answer to generate a larger numbers tables

Upvotes: 0

mellamokb
mellamokb

Reputation: 56769

Disclaimer: Storing comma-separated lists in a database column is a code smell, and generally indicates you need to normalize your database structure. Please do not do this in your code if at all possible.

You can solve by matching client id values inside the comma-separated list with LIKE clause:

select
  t2.ID, t1.CompId
from Tbl1 t1
join Tbl2 t2 on t1.CommaList
  like '%''' + cast(t2.ClientId as varchar(10)) + '''%'

Demo: http://www.sqlfiddle.com/#!3/6d416/6

Upvotes: 3

www
www

Reputation: 4391

Try this:

select tbl1.CompId, tbl2.Id
from tbl1, tbl2 
where CHARINDEX('''' + CONVERT(varchar(50),tbl2.ClientId) + '''', CommaList)<> 0

Upvotes: 1

Dave Sexton
Dave Sexton

Reputation: 11188

I have posted a solution which I haven't tested but should work. It feels wrong doing the way and I now feel unclean by encouraging you with an answer:

SELECT tbl1.CompId, tbl2.Id
FROM tbl1  
  JOIN tbl2 ON CommaList LIKE '%''' + CAST(tbl2.ClientId AS VARCHAR) + '%'''

Upvotes: 2

Andrew Lazarus
Andrew Lazarus

Reputation: 19320

Almost certainly you want to do this with a join table, expanding CommaList. That is, you want a table with

 Tbl3
 CompID      ClientID
 2            122
 2             54
 2             90
 54            53

etc.

Some databases let you store CommaList as an array and use an IN operator, but that's both non-standard and inefficient. (You can also do a string search like mellamokb's answer, but notice his disclaimer is the same as mine.) Now you can just do

SELECT id, compID FROM Tbl3 NATURAL JOIN Tbl2;

Except in special cases, avoid denormalized data like Tbl1.

Upvotes: 2

Related Questions