1

I have a query where I have to join two tables. Lets say T1 and T2. T1 has a column with an Id. And every row has only one Id value. However, the second table is where i'm struggling. in T2 there is a column with the Id's but it can be possible that one row has multiple of those Id's. So as example T1.col1 has this value: 737382. But T2.col2 can have this entries in one row: 737382;239112;2838210;9923834;2388342;...

I know that this structure violates the 1NF and stuff. But i cannot change anything in the data or structure of the data. now what i want to do is to join this two tables. Please let me know if i'm missing to tell any relevant information in order to answer my question. its late and my brain is exhausted ~.~

Improve this question

5 Answers 5

Reset to default
0

try 

    select tab2.*   -- whatever
      from t1 tab1
inner join t2 tab2 on ( ';'||tab2.col2||';' like '%;'||tab1.col1||';%' )
         ;

the extra affixed ; characters serve to avoid disjunctions in the join condition.

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

Thanks for your reply. it seems simple so i decided to try it. however i use MSSQL Server 2008 and it doesnt work with the || operator. Tbh i dont even know what it means. could you tell me what the meaning of it is, so i can mark your post as answer? (as long as it will work) best regards
the || operator is equivalent to a concat call.
thanks, i've figured it out to myself by now.. a shame i didnt get it earlier :/
And yes, your answer works perfectly... just needs its time to execute. :)
0

You could use regular expressions in your join, your regular expression can check for your T1.col1 in T2.col2. The regular expression should check for the value from the begining of the string (i.e. T2.col2) or being preceeded by ';' and always followed by ';'

Improve this answer

Comments

0

Have you tried something like:

select 
    a.column1, 
    a.column2, 
    b.column1,
    b.column2 
from table a 
inner join table b on a.column1 = b.column1
Improve this answer

Comments

0

Since one T2.Col2 can hold n entries, you need to parse them to rows using a table-valued function and then using CROSS APPLY

BEWARE if the T2 is big this solution will hang for quite long time

something like this:

;WITH IDSplitted AS 
(
   SELECT * 
   FROM  T2
   CROSS APPLY dbo.[StringSplit](col2, ';')
)

SELECT * -- or whatever columns you need
FROM T1
INNER JOIN IDSplitted  ON IDSplitted.val = t1.col1

having StringSplit:

CREATE FUNCTION [dbo].[StringSplit]
(
  @delimited nvarchar(max),
  @delimiter nvarchar(100)     
) RETURNS @t TABLE
(
-- Id column can be commented out, not required for sql splitting string
  id int identity(1,1), -- I use this column for numbering splitted parts
  val nvarchar(max)
)
AS
BEGIN
  declare @xml xml
  set @xml = N'<root><r>' + replace(@delimited,@delimiter,'</r><r>') + '</r></root>'

  insert into @t(val)
  select 
    r.value('.','varchar(max)') as item
  from @xml.nodes('//root/r') as records(r)

  RETURN
END
Improve this answer

1 Comment

Thanks for your answer! i'll take a look as soon as i return from vacations and can try it out!
0

After using the solution of collapstar (which was correct) i encountered performance issues. so what i did is to create a mapping table so that when i have to run the query again, i dont have to wait so long. so i have a scheduled job which does the join and writes the output into a mapping table over night

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.