May 24, 2010 at 12:24 pm
Hi i got three tables table1, table2 and table3
like
TABLE1
create table1
(
source varchar(500)
)
insert into table1(source)
values('c:\abcd\sddf\dff\dff\d\df')
insert into table1(source)
values('d:\sf\df\eef\cvf\')
insert into table1(source)
values('\\abc.id.do.k\efl\i\d\re')
insert into table1(source)
values('\\cd\asd\fgc\dfr\g')
insert into table1(source)
values('c:\hgj\sf\hj\dfg\d\df')
Now TABLE2
create table2
(
loc varchar(500)
)
insert into table2(loc)
values([sdff]\\dff\dsff\dfd\sdd)
insert into table2(loc)
values('[asco]\\dg\jk\ok\olf\')
insert into table2(loc)
values('[ddgf]\\abc\efl\i\lk\ki')
insert into table2(loc)
values('[dsfvdf]\\cd\asd\fgc\th\lkfj')
table3
create table3(
id varchar(100)
)
insert into table3(id)
values(dff)
insert into table3(id)
values(dfc)
insert into table3(id)
values(hj)
insert into table3(id)
values(df)
Now the three tables will be like
Table1
source
--------
c:\abcd\df\dff\dff\d\df
d:\sf\df\eef\cvf\
\\abc.id.do.k\efl\i[/b]\d\re
\\cd\asd\fgc\dfr\g
c:\hgj\df\hj\dfg\d\df
Table2
loc
----
[sdff]\\dff\dsff\dfd\sdd
[asco]\\dg\jk\ok\olf\
[ddgf]\\abc\efl\i\lk\ki
[dsfvdf]\\cd\asd\fgc\th\lkfj
Table3
id
----
dff
dfc
hj
df
Now i need to join the table1 with the other two table based on different conditions
i want to get the recorde from all the three tables where
case1:
if that the part of string from first \ to the end of fourth \
for example take fourth row of column source in table 1
\\cd\asd\fgc\dfr\g so i need the highlighted part that is from first'\' to end of fourth '\'
and that is to be compared with the rows in table2 as of above like from the first '\' to end of fourth '\'
so in brief
table1 table2
\\cd\asd\fgc\dfr\g [dsfvdf]\\cd\asd\fgc\th\lkfj'
so when u closely look at the above values the highlighted part of the string in table1 is equal to the highlighted part of string in table2 , so that rows should be returned with inner join
And one more condition when you look at row3 in table1 i got it like
\\abc.id.do.k\efl\i\d\re
here i need to delete the part of string from first '.' to end of last '.'
so when i trim that part it will be like \\abc\efl\i\d\re .So now i can perform the compare operation from first '\' to the end of fourth '\' with the row in table 2.
i got nearly 2million records like that in table1 and table2 so i jus took some of them
OR
case2:
where the highlighted part in table 1 for the columns whihc got df in it
for example
considerfirst row in table 1
c:\abcd\df\dff\dff\d\df
it got df in the underlined part
so we will consider that row so in that the highlighted part of string should be equal to the ID in table3
so i need to write some join condition whihc will get me all records based on conditions
which satisfies
case1 OR case2
May 24, 2010 at 1:54 pm
Don't get me wrong but I think you should get a consultant in to help you resolving that puzzle as a whole.
It's the 3rd or 4th post regarding the same issue. As soon as one issue is resolved you're posting the next question. (I figured by recognizing the data structure of the question together with your recent posts...).
We're here to help you. Not to replace you...
May 24, 2010 at 2:14 pm
lmu92 (5/24/2010)
Don't get me wrong but I think you should get a consultant in to help you resolving that puzzle as a whole.It's the 3rd or 4th post regarding the same issue. As soon as one issue is resolved you're posting the next question. (I figured by recognizing the data structure of the question together with your recent posts...).
We're here to help you. Not to replace you...
I think a consultant would be a good option for this series of questions.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply