Juan Carlos Oropeza Juan Carlos Oropeza - 4 months ago 32
C# Question

Self JOIN linq with not equal

myTuples has

{string id, int start, int end}


With this sample data:

{A, 10, 11},
{B, 20, 30},
{C, 25, 35},
{D, 25, 28},
{E, 7, 35},


The result should be:
x1 < x2 < x3 < x4


{7, 10, 11, 35} -- row id=A {10, 11} between id=E {7,35}
{7, 25, 28, 35} -- row id=D {25, 28} between id=E {7,35}
{20, 25, 28, 30} -- row id=D {25, 28} between id=B {20,30}


If you add
{F, 15, 40}
then
row=B
can be in the inside too.

{15, 20, 30, 40} -- row id=B {20, 30} between id=F {15,40}


This is what I have try.

var query = from t1 in myTuples
join t2 in myTuples
on t1.id equals t2.id
where (t1.start > t2.start && t1.end < t2.end)
|| (t1.start < t2.start && t1.end > t2.end)
select new
{
x1 = t1.start,
x2 = t2.start,
x3 = t1.end,
x4 = t2.end
};


But my first problem is there isnt
not equal
join.

The last part doesnt matter much I can fix it later. But Im thinking something like

select new
{
x1 = t1.start < t2.start : t1.start : t2.start,
x2 = t1.start < t2.start : t2.start : t1.start,
x3 = t1.end < t2.en: t1.end: t2.end,
x4 = t1.end < t2.en: t2.end: t1.end
};

Answer

Having defined

 var myTuples = new Tuple<string,int,int>[5] {
new Tuple<string,int,int>("A",10,11), new Tuple<string,int,int>("B",20,30),
new Tuple<string,int,int>("C",25,35), new Tuple<string,int,int>("D",25,28),
new Tuple<string,int,int>("E",7,35) };

I can do a self join with not equal using SelectMany.

     var selfJoinNotEqual = myTuples
.SelectMany( x => myTuples.Where(y => y.Item1 != x.Item1).Select( y => new { x, y}));

For the second part, add another select

.Select(z => new {  
    x1 = ( z.x.Item2 <= z.y.Item2 ? z.x.Item2 : z.y.Item2),
    x2 = (z.x.Item2 <= z.y.Item2 ? z.y.Item2 : z.x.Item2) ,
    x3 = (z.x.Item3 <= z.y.Item3 ? z.x.Item3 : z.y.Item3),
    x4 = (z.x.Item3 <= z.y.Item3 ? z.y.Item3 : z.x.Item3) 
})