none
Multiple join select 1st record on 1 of the table RRS feed

  • Question

  • Hi,

    I have Table 1, 2, 3,  I try to join 3 tables and only select first record of table 3,

    Eg.

    Table 1:
    ID   Name
    -----------
    1     Peter
    
    Table 2:
    ID   Role        Name
    ----------------------
    1    Admin       Peter
    2    Supervisor  Peter 
    
    Table 3:
    ID   Task              Name
    ----------------------------
    1    Data Entry        Peter
    2    Data Collection   Peter 
    3    Data Analysis     Peter
    4    Data Compilation  Peter
    

    Expected Result:

    ID     Name       Role               Task

    -----------------------------------------------

    1      Peter        Admin              Data Entry

    2      Peter        Supervisor       Data Entry

    I would like to only select 1st record from table 3 and join into table 1 & 2, anyone can guide me on how to accomplished this using Linq?

    Thanks.

    Regards,

    Kong CA



    • Edited by kong5091 Wednesday, January 20, 2016 3:14 AM
    Wednesday, January 20, 2016 3:13 AM

Answers

  • Hi kong5091,

    >> Thanks for ur reply, is there any way to select the 1st record from table 3 instead of using "where p.id == 1"?

    You could try to modify the code as below, and it works fine on my side.

    var result = from t2 in db.Table2s
                 from t3s in (
                  from t3 in db.Table3s
                  join t1 in db.Table1s on t3.Name equals t1.Name
                  where t3.Name == t2.Name
                  orderby t3.Id
                  select new
                   {
                      t3.Id,
                      t3.Task,
                      t1.Name
                   }).Take(1).DefaultIfEmpty()
                   select new
                   {
                      ID = t2.Id,
                      Name = t3s.Name,
                      Role = t2.Role,
                      Task = t3s.Task
                   };
    foreach (var item in result)
    {
        Console.WriteLine("{0} --- {1} ---- {2} ---- {3}", item.ID, item.Role, item.Name, item.Task);
    }
    

    I hope it’s helpful to you.

    Best regards,

    Cole Wu

    Thursday, January 21, 2016 3:26 AM
    Moderator

All replies

  • Hi kong5091,

    According to your description, I create a demo as below for your reference.

    using (var db = new TestDataContext())
    
                {
    
                    var query = from p in db.Table3s
    
                                join a in db.Table2s on p.Name equals a.Name
    
                                join b in db.Table1s on p.Name equals b.Name
    
                                where p.Id == 1
    
                                select new
    
                                {
    
                                    Id = p.Id,
    
                                    Name = b.Name,
    
                                    Role = a.Role,
    
                                    Task = p.Task
    
                                   
    
                                };
    
                    foreach (var item in query)
    
                    {
    
                        Console.WriteLine("{0} --- {1} ---- {2} ---- {3}", item.Id, item.Role, item.Name, item.Task);
    
                    }
    
                    Console.ReadLine();
    
                }
    

    I hope it’s helpful to you.

    Best regards,

    Cole Wu
    Wednesday, January 20, 2016 7:08 AM
    Moderator
  • Hi kong5091,

    According to your description, I create a demo as below for your reference.

    using (var db = new TestDataContext())
    
                {
    
                    var query = from p in db.Table3s
    
                                join a in db.Table2s on p.Name equals a.Name
    
                                join b in db.Table1s on p.Name equals b.Name
    
                                where p.Id == 1
    
                                select new
    
                                {
    
                                    Id = p.Id,
    
                                    Name = b.Name,
    
                                    Role = a.Role,
    
                                    Task = p.Task
    
                                   
    
                                };
    
                    foreach (var item in query)
    
                    {
    
                        Console.WriteLine("{0} --- {1} ---- {2} ---- {3}", item.Id, item.Role, item.Name, item.Task);
    
                    }
    
                    Console.ReadLine();
    
                }

    I hope it’s helpful to you.

    Best regards,

    Cole Wu

    Hi Cole,

    Thanks for ur reply, is there any way to select the 1st record from table 3 instead of using "where p.id == 1"?

    Regards,

    Kong CA

    Wednesday, January 20, 2016 8:13 AM
  • Hi kong5091,

    >> Thanks for ur reply, is there any way to select the 1st record from table 3 instead of using "where p.id == 1"?

    You could try to modify the code as below, and it works fine on my side.

    var result = from t2 in db.Table2s
                 from t3s in (
                  from t3 in db.Table3s
                  join t1 in db.Table1s on t3.Name equals t1.Name
                  where t3.Name == t2.Name
                  orderby t3.Id
                  select new
                   {
                      t3.Id,
                      t3.Task,
                      t1.Name
                   }).Take(1).DefaultIfEmpty()
                   select new
                   {
                      ID = t2.Id,
                      Name = t3s.Name,
                      Role = t2.Role,
                      Task = t3s.Task
                   };
    foreach (var item in result)
    {
        Console.WriteLine("{0} --- {1} ---- {2} ---- {3}", item.ID, item.Role, item.Name, item.Task);
    }
    

    I hope it’s helpful to you.

    Best regards,

    Cole Wu

    Thursday, January 21, 2016 3:26 AM
    Moderator