Hello to all,
I have a problem with ms sql query. I hope that somebody can help me.
i have a table "Relationships". There are two Fields (IDMember und RelationshipIDs) in this table. IDMember is the Owner ID (type: integer) und RelationshipIDs saves all partners of this Owner ( type: varchar(1000)). Example Datas for Table Relationships: IDMember Relationships .
3387 (2345, 2388,4567,...)
4567 (8990, 7865, 3387...)
i wirte a query to check if there is Relationship between two members.
Query:
Declare @.IDM int; Declare @.IDO int; Set @.IDM = 3387, @.IDO = 4567;
select *
from Relationship
where(IDMember= @.IDM)and( cast(@.ID0 as char(100)) in
(select Relationship.[RelationshipIDs]from Relationshipwhere IDMember= @.IDM))
But I get nothing by this query.
Can Someone tell me where is the problem? Thanks
Best Regards
Pinsha
Use the PATINDEX function to check if a one part is there in a string or not and you can write your query like this
Select * from RelationShip
Where (IDMember=@.IDM)
AND ( PATINDEX(@.IDO, RelationshipIDs) > 0 ) -- If the string is found this will return greater than 0 the starting of the string
This should help you
|||
try this, you have to tweak it a little to work correctly with small and big numbers ( like it will return 312 as connected to 12 but you can try to fix it your self for example by including separators between related ids)
createTable #Relationships
(IDMemberint,
Relationshipsvarchar(1000))
insertinto #Relationships
select' 3387','(2345, 2388,4567,...)'
insertinto #Relationships
select'4567','(8990, 7865, 3387...)'
Declare @.IDMint,
@.IDOint
Set @.IDM= 3387
SET @.IDO= 4567;
select*
from #Relationships
where(IDMember= @.IDM)and(charindex(cast(@.IDOasvarchar(100)),cast(Relationshipsasvarchar(1000)))>0)
droptable #Relationships
|||
Hello satya_tanwar,
i have tried to use the PATINDEX function, but if i used a variable in PATINDEX(@.IDO, RelationshipIDs) and (@.IDO = '3456', i doesn't work. If i use a constant in PATINDEX('3456', RelationshipIDs), it works.
I use ms sql 2005 to execute this query. Maybe can you tell me where is the problem?
Thank you very much
Best Regards
Pinsha
|||
Hallo japzgier,
I have also tried to use your example. But the variable incharindex function doesn't work. If i use constant incharindex function and it works.
i don't know whycharindex doesn't work with variable.
Maybe can you help me?
Thank you very much
Best Regards
Pinsha
|||
What is the variable type Pinsha...Could you please send the code to me coz i have tried to execute the query and its working. Whats result you are getting ?
or just try to modify the PatIndex part like this
(PATINDEX('%'+ @.IDO+'%', Relationships)> 0)
Will work for sure..
|||
I tested code I passed on my SQL server and I have no problems with it, I use SQL 2005 which version are you using?
|||
Hello to japzgier,
I found the straight problem. The problem is false data type. i habe defined a variable as char type and another variable as varchar type.
i use ms sql 2005.
Thanks very much
But i have now another problem, Maybe could you help me?
In my tablewtcomValidRelationships
IDMember RelationshipIDS
1 2
2 3
3 4
4 5
5 6
declare @.ENDPATH varchar(1000);
declare @.IDMint;
declare @.IDO varchar(100);
set @.IDM= 1;
set @.IDO='6'
declare @.Path3 varchar(1000);
set @.Path3=(selecttop 1convert(varchar(100),A.IDMember)+'-'+convert(varchar(100),B.IDMember)+'-'+convert(varchar(100),C.IDMember)+'-'+convert(varchar(100),D.IDMember)+'-'+convert(varchar(100),E.IDMember)
from wtcomValidRelationshipsas A,
wtcomValidRelationshipsas B,
wtcomValidRelationshipsas C,
wtcomValidRelationshipsas D,
wtcomValidRelationshipsas E
where A.IDMember= @.IDMandcharindex(convert(varchar(100),B.IDMember),A.RelationshipIDS)> 0
andcharindex(convert(varchar(100),C.IDMember),B.RelationshipIDs)> 0
andcharindex(convert(varchar(100),D.IDMember), C.RelationshipIDs)> 0
andcharindex(convert(varchar(100),E.IDMember),D.RelationshipIDs)> 0
andcharindex(@.IDO,E.RelationshipIDs)> 0);
if(len(@.Path3)> 0)
begin
set @.ENDPATH= @.Path3;
end
else
begin
set @.ENDPATH='No Relationship';
end
print @.ENDPATH
Problem is that Query Excuting need to 38 Secends ?
Thanks
Best Regards
Pinsha
|||
Hi sasha,
Have you tried this
(PATINDEX('%'+ @.IDO+'%', Relationships)> 0)
|||
Hello satya,
i have tried this (PATINDEX('%'+ @.IDO+'%', Relationships)> 0) , it works. Thanks
but now i have another problem.
I need to search the relationships from level 1 to level 6.
so i need write aIF ELSE sql query. But when i useIF ELSE, the query excuting is too slow. without if else, the query excuting is rash.
But i must useIF ELSE to get my result. Maybe Could you tell me other methods, how can i check the first level query, if it has no elements and then go to excute next level , and continue do this compare. My complete Codes are here. I hope that you can understand what i mean. Because my english is poor . sorry
For Example
wtcomValidRelationships
IDMember RelationshipIDs
1 2
2 3
3 4
4 5
5 6
Without IF ELSE 5 select Query together excuted under 1 Seconds:
declare @.ENDPATH varchar(1000);
declare @.IDMint;
declare @.IDO varchar(100);
set @.IDM= 1;
set @.IDO='6'
select IDMember
from wtcomValidRelationships
where wtcomValidRelationships.[IDMember]= @.IDM
andPATINDEX('%'+@.IDO+'%',(select RelationshipIDsfrom wtcomValidRelationshipswhere IDMember= @.IDM))> 0
selecttop 1convert(varchar(100),A.IDMember)+'-'+convert(varchar(100),B.IDMember)
from wtcomValidRelationshipsas A, wtcomValidRelationshipsas B
where A.IDMember= @.IDMandcharindex(convert(varchar(100),B.IDMember),A.RelationshipIDS)> 0
andPATINDEX('%'+@.IDO+'%',B.RelationshipIDs)> 0
selecttop 1convert(varchar(100),A.IDMember)+'-'+convert(varchar(100),B.IDMember)+'-'+convert(varchar(100),C.IDMember)
from wtcomValidRelationshipsas A, wtcomValidRelationshipsas B, wtcomValidRelationshipsas C
where A.IDMember= @.IDMandcharindex(convert(varchar(100),B.IDMember),A.RelationshipIDS)> 0
andcharindex(convert(varchar(100),C.IDMember),B.RelationshipIDs)> 0andPATINDEX('%'+@.IDO+'%',C.RelationshipIDs)> 0
selecttop 1convert(varchar(100),A.IDMember)+'-'+convert(varchar(100),B.IDMember)+'-'+convert(varchar(100),C.IDMember)+'-'+convert(varchar(100),D.IDMember)
from wtcomValidRelationshipsas A, wtcomValidRelationshipsas B, wtcomValidRelationshipsas C, wtcomValidRelationshipsas D
where A.IDMember= @.IDMandcharindex(convert(varchar(100),B.IDMember),A.RelationshipIDS)> 0
andcharindex(convert(varchar(100),C.IDMember),B.RelationshipIDs)> 0andcharindex(convert(varchar(100),D.IDMember), C.RelationshipIDs)> 0
andPATINDEX('%'+@.IDO+'%',D.RelationshipIDs)> 0
selecttop 1convert(varchar(100),A.IDMember)+'-'+convert(varchar(100),B.IDMember)+'-'+convert(varchar(100),C.IDMember)+'-'+convert(varchar(100),D.IDMember)+'-'+convert(varchar(100),E.IDMember)
from wtcomValidRelationshipsas A, wtcomValidRelationshipsas B, wtcomValidRelationshipsas C, wtcomValidRelationshipsas D, wtcomValidRelationshipsas E
where A.IDMember= @.IDMandcharindex(convert(varchar(100),B.IDMember),A.RelationshipIDS)> 0
andcharindex(convert(varchar(100),C.IDMember),B.RelationshipIDs)> 0andcharindex(convert(varchar(100),D.IDMember), C.RelationshipIDs)> 0
andcharindex(convert(varchar(100),E.IDMember),D.RelationshipIDs)> 0andPATINDEX('%'+@.IDO+'%',E.RelationshipIDs)> 0
But With IF ELSE query excuted more than 30 seconds
declare @.ENDPATH varchar(1000);
declare @.IDMint;
declare @.IDO varchar(100);declare @.Level int;
set @.IDM= 3450;
set @.IDO='4269'
declare @.Path varchar(1000);
set @.Path='';
set @.Path=(select IDMember
from wtcomValidRelationships
where wtcomValidRelationships.[IDMember]= @.IDM
andcharindex( @.IDO,(select RelationshipIDsfrom wtcomValidRelationshipswhere IDMember= @.IDM))> 0);
if(len(@.Path)> 0)
begin
set @.ENDPATH= @.Path;
end
else
begin
declare @.Path1 varchar(1000);
set @.Path1=(selecttop 1convert(varchar(100),A.IDMember)+'-'+convert(varchar(100),B.IDMember)
from wtcomValidRelationshipsas A, wtcomValidRelationshipsas B
where A.IDMember= @.IDMandcharindex(convert(varchar(100),B.IDMember),A.RelationshipIDS)> 0
andcharindex(@.IDO,B.RelationshipIDs)> 0);
if(len(@.Path1)> 0)
begin
set @.ENDPATH= @.Path1;
end
else
begin
declare @.Path5 varchar(1000);
set @.Path5=(selecttop 1convert(varchar(100),A.IDMember)+'-'+convert(varchar(100),B.IDMember)+'-'+convert(varchar(100),C.IDMember)
from wtcomValidRelationshipsas A, wtcomValidRelationshipsas B, wtcomValidRelationshipsas C
where A.IDMember= @.IDMandcharindex(convert(varchar(100),B.IDMember),A.RelationshipIDS)> 0
andcharindex(convert(varchar(100),C.IDMember),B.RelationshipIDs)> 0andcharindex(@.IDO,C.RelationshipIDs)> 0);
if(len(@.Path5)> 0)
begin
set @.ENDPATH= @.Path5;
end
else
begin
declare @.Path2 varchar(1000);
set @.Path2=(selecttop 1convert(varchar(100),A.IDMember)+'-'+convert(varchar(100),B.IDMember)+'-'+convert(varchar(100),C.IDMember)+'-'+convert(varchar(100),D.IDMember)
from wtcomValidRelationshipsas A, wtcomValidRelationshipsas B, wtcomValidRelationshipsas C, wtcomValidRelationshipsas D
where A.IDMember= @.IDMandcharindex(convert(varchar(100),B.IDMember),A.RelationshipIDS)> 0
andcharindex(convert(varchar(100),C.IDMember),B.RelationshipIDs)> 0andcharindex(convert(varchar(100),D.IDMember), C.RelationshipIDs)> 0
andcharindex(@.IDO,D.RelationshipIDs)> 0);
if(len(@.Path2)> 0)
begin
set @.ENDPATH= @.Path2;
end
else
begin
declare @.Path3 varchar(1000);
set @.Path3=(selecttop 1convert(varchar(100),A.IDMember)+'-'+convert(varchar(100),B.IDMember)+'-'+convert(varchar(100),C.IDMember)+'-'+convert(varchar(100),D.IDMember)+'-'+convert(varchar(100),E.IDMember)
from wtcomValidRelationshipsas A, wtcomValidRelationshipsas B, wtcomValidRelationshipsas C, wtcomValidRelationshipsas D, wtcomValidRelationshipsas E
where A.IDMember= @.IDMandcharindex(convert(varchar(100),B.IDMember),A.RelationshipIDS)> 0
andcharindex(convert(varchar(100),C.IDMember),B.RelationshipIDs)> 0andcharindex(convert(varchar(100),D.IDMember), C.RelationshipIDs)> 0
andcharindex(convert(varchar(100),E.IDMember),D.RelationshipIDs)> 0andcharindex(@.IDO,E.RelationshipIDs)> 0);
if(len(@.Path3)> 0)
begin
set @.ENDPATH= @.Path3;
end
else
begin
set @.ENDPATH='No Relationship'
end
end
end
end
end
print @.ENDPATH
