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

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)

Answer 1

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

Answer 2

User2131089582 posted

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

Answer 3

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

Answer 4

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();