I have 2 result from diferent tables whith same father table id and i try return in only 1 dataset the structure is something like that
Table 1
| id1 | dMaster | detailMaster | Note | typeNote | info |
|---|---|---|---|---|---|
| 1 | 552 | 10 Mhz | 1 | 3 | is the note1 |
| 2 | 552 | 10 Mhz | 2 | 3 | is the note2 |
| 3 | 552 | 10 Mhz | 3 | 1 | is the note3 |
| 4 | 554 | 20 Mhz | 3 | 1 | is the note3 |
Table 2
| id2 | IdMaster | detailMaster | Service | typeService | info |
|---|---|---|---|---|---|
| 1 | 552 | 10 Mhz | 1 | 3 | is the Service1 |
| 2 | 552 | 10 Mhz | 2 | 3 | is the Service2 |
| 3 | 552 | 10 Mhz | 3 | 1 | is the Service3 |
| 4 | 553 | 15 Mhz | 3 | 1 | is the Service3 other id |
And i try return something like that
| IdMaster | detailMaster | Note | typeNote | info | Service | typeService | info |
|---|---|---|---|---|---|---|---|
| 552 | 10 Mhz | 1 | 3 | is the note1 | 1 | 3 | is the Service1 |
| 552 | 10 Mhz | 2 | 3 | is the note2 | 2 | 3 | is the Service2 |
| 552 | 10 Mhz | 3 | 1 | is the note3 | 3 | 1 | is the Service3 |
| 553 | 15 Mhz | null | null | null | 3 | 1 | is the Service3 other id |
| 554 | 20Mhz | 1 | 3 | is the note1 other id | null | null | null |
Note and service be an optional, is the reason the null value in the result, but always have one
i hope can help me using sql or linq
thanks a lot
P.D.: by the way Sorry english google translate =P
-----More details ------
In this moment i try 2 ways to resolved this problem using tsql and linq
let me show my approximations to solve
solution Joins (inner - left - full -cross or where) Simplified cartesian result
| idMaster | Note | TypeNote | Service | typeService |
|---|---|---|---|---|
| 552 | 1 | 3 | 1 | 3 |
| 552 | 1 | 3 | 2 | 3 |
| 552 | 1 | 3 | 3 | 1 |
| 552 | 2 | 3 | 1 | 3 |
| 552 | 2 | 3 | 2 | 3 |
| 552 | 2 | 3 | 3 | 1 |
| 552 | 3 | 1 | 1 | 3 |
| 552 | 3 | 1 | 2 | 3 |
| 552 | 3 | 1 | 3 | 1 |
the "solution" I have at the moment is using union Simplified cartesian result
| idMaster | Note | TypeNote | Service | typeService |
|---|---|---|---|---|
| 552 | NULL | NULL | 1 | 3 |
| 552 | NULL | NULL | 2 | 3 |
| 552 | NULL | NULL | 3 | 1 |
| 552 | 1 | 3 | NULL | NULL |
| 552 | 2 | 3 | NULL | NULL |
| 552 | 3 | 1 | NULL | NULL |
On the other hand using linq, i am try different ways union, join and 2 for to merge the results
The classes I use are the following
AKA table1
public class notas
{
public int IdRangoRegion2 { get; set; }
public string Frecuencia { get; set; }
public int? IdRangoNota { get; set; }
public int? IdNotaRango { get; set; }
public int? IdTipoNota { get; set; }
public string DescripcionTipoNotaRango { get; set; }
}
AKA table2
public class servicios
{
public int IdRangoRegion2 { get; set; }
public string Frecuencia { get; set; }
public int? IdRangoXServicio { get; set; }
public int IdCategoriaServicio { get; set; }
public string TipoCategoria { get; set; }
public int? IdServicio { get; set; }
public string DescripcionNotaServicio { get; set; }
public int? IdNotaServicio { get; set; }
public string Titulo { get; set; }
}
AKA Result expected (merge the other classes )
public class Listado
{
public int IdRangoRegion2 { get; set; }
public string Frecuencia { get; set; }
public int? IdRangoNota { get; set; }
public int? IdNota { get; set; }
public int? IdTipoNota { get; set; }
public string DescripcionTipoNotaRango { get; set; }
public int? IdRangoXServicio { get; set; }
public int? IdCategoriaServicio { get; set; }
public string TipoCategoria { get; set; }
public int? IdServicio { get; set; }
public string DescripcionNotaServicio { get; set; }
public int? IdNotaServicio { get; set; }
public string Titulo { get; set; }
}
I hope that with this extra information my problem will be clearer, thanks