How to Stop Multiple Combination in Self Join
-
I hav a table like this Table T (ID,ID2,Data) Values((1,N,Data) ,(2,N,Data ),(3,C,Data ), (4,N,Data5 ) ,(5,C,Data) ,(6,N,Data7))
Now I Want to get result set like 1 - 3 , 2-5
Since Data Is Same (Inner Join Attributes) And ID2 are opposite (C Vs N) Needed.
But after self join result is like 1 - 3 , 2 - 5 , 1 - 5 , 2 - 3 . Can anyone tell me how to stop multiple combination from this .
-
One idea could be to use a tmp table where you insert your result from your inner join and then delete the results you do not want...
-
The question is now : how do you know which records that you do no want?
-
Hi Remy ,
Right Question , But i want any one combination . When one combination is mapped, there should not be another combination As 1 - 3, 2 - 5, 1 - 5 , 2 - 3 in resultset. By Loop my senior has resolved this problem . Any other method will be appriciated.
Thanks
-
Maybe if you can answer this :
how do you invalidate 1 - 5 , 2 - 3 from
1 - 3 , 2 - 5, 1 - 5 , 2 - 3
??
This is a pretty simple self join, but I join can't picture the final condition. Help!!
-
SET NOCOUNT ON
DECLARE @T Table (ID int, ID2 char(1) , Data varchar(10))
insert into @T (ID,ID2,Data) Values(1,'N','Data')
insert into @T (ID,ID2,Data) Values(2,'N','Data' )
insert into @T (ID,ID2,Data) Values(3,'C','Data' )
insert into @T (ID,ID2,Data) Values(4,'C','Data5' )
insert into @T (ID,ID2,Data) Values(5,'C','Data')
insert into @T (ID,ID2,Data) Values(6,'N','Data7')
insert into @T (ID,ID2,Data) Values(7,'N','Data5' )
select * from @t
--SELECT AA.LineNumber,AA.ID2, AA.Data,AA.ID,BB.LineNumber,BB.ID2, BB.Data,BB.ID
SELECT AA.ID,BB.ID,AA.ID2, BB.ID2, BB.Data
FROM
(SELECT COUNT(*) LineNumber, A.ID2 ,A.Data, A.ID
FROM @T A join @T B on A.ID>=B.ID AND A.ID2=B.ID2 AND A.Data=B.Data
where A.Id2='N' AND B.ID2='N'
Group by A.Data,A.ID2,A.ID) AA
INNER JOIN
(SELECT COUNT(*) LineNumber, A.ID2 ,A.Data, A.ID
FROM @T A join @T B on A.ID>=B.ID AND A.ID2=B.ID2 AND A.Data=B.Data
where A.Id2='C' AND B.ID2='C'
Group by A.Data,A.ID2,A.ID) BB
ON AA.LineNumber=BB.LineNumber and AA.Data=BB.Data and AA.ID2<>BB.ID2
Kindest Regards,Vasc
-
Wow... can you explain the logic of this one??
-
The catch is to label the rows for each DATA and ID2 with Ordered Numbers and match the rows with diff ID2 but the same Data and RowNumber
Kindest Regards,Vasc
-
I'll sleep on it and watch it again in the morning
. -
Hi Rookie,
Fantastic ! U hav made proper solution .
Thanks Again.
-
What would the query look like if you had C,N,Y,O as possible id2?
Would you have 4 derived tables now?
-
Won't work to have 4 derived tables.
The solution is based on comb of two diff sets.
so if C,N,Y,O form 2 sets the sol will work
ex: C,N can be combined with either Y,O but not with other C,N value Sol will work
If you further say that you can conbine C with N won't work.
Kindest Regards,Vasc
-
I see, this is the kindof solution that can be usefull but on a small scale... Thanks again for the info.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply