locked
How can i select one to many relationship in table with separated values by commas RRS feed

  • Question

  • User2131089582 posted

    How can i select one to many relationship in table with separated values by commas


    I have already tried using below query, unfortunately i don't get what i expect

    select mStudent.Id as Id, mStudent.Name as Name, mAddress.Name as Addresses
    from Students mStudent, Addresses mAddress
    where mAddress.StudentId=mStudent.Id, mStudent.Id=mStudentId

    and notice i want also to show the table which does not have Address (null)

    Sunday, September 29, 2019 9:33 AM

All replies

  • User-474980206 posted

    sql is a set language and only returns sets. you can do a left jpin for no matches:

    select mStudent.Id as Id, mStudent.Name as Name, mAddress.Name as Addresses
    from Students mStudent
     left join Addresses mAddress
        on mAddress.StudentId=mStudent.Id
    

    as sql returns a set, you sill get a row for each match. to get the addresses as a comma separated list, will require a hierarchical query language. you can do this with linq group by after performing the sql query.

    if you using sqlserver 2017 + you can use a special function to do this:

    select id, 
           name,
           address = (select STRING_AGG(name, ', ')
               from address a
               where a.studentid = s.id
           )
    from student s

    Sunday, September 29, 2019 7:45 PM
  • User2131089582 posted

    i don't understand how i can do with linq group by after performing the sql query

    Monday, September 30, 2019 12:15 AM
  • User-17257777 posted

    Hi hocamahdi99,

    According to your description, I suggest you could try to use For XML Path and Stuff function to achieve your requirement.

    “For XML Path” allows you to output the results of the query as XML elements, with the element name contained in the PATH argument, then use “Stuff” here can replace the first comma.

    Select  s.Id, s.Name As Name,
            Stuff((
                Select  ',' + c.Name
                From    Addresses c
                Where   s.Id = c.Student_Id
                For     XML Path('')
            ), 1, 1, '') As Address
    From    Students s
    Group By s.Id, s.Name
    

    Test Result:

    Best Regards,

    Jiadong Meng

    Monday, September 30, 2019 9:03 AM
  • User-1038772411 posted

    try this out

    var Students = (from s in db.Students
    join a in db.Addresses on s.Id equals a.StudentId
    where a.StudentId == s.Id
    select new FinalStudent()
    {
    Name=s.Name,
    Address=a.Name
    }).AsEnumerable().ToList();
    
    var list =(from i in Students group i by i.Name into g
    select new { g.Key, count = g.Count(), Items = string.Join(",", g.Select(kvp => kvp.Name)) }).ToList();

    Monday, November 11, 2019 11:41 AM