Due to some inheritance limitations when using Linq – SQL I have recently needed to do something quite strange in a SQL View which made more sense in Linq. However I never say no to a SQL challange and a few horus later a rather beautiful (in my opinion) solution was finished.
First lets look at some example data for my problem. The following data is a table off ladies attending a Mother – Daughter Event
The view we would like to get is something like this
SELECT people.name AS Name,
people.mother AS Mother,
LEFT OUTER JOIN people people_inner_selfjoin
ON people_inner.name= people_inner_selfjoin.mother
WHERE people.name = people.mother
FOR XML PATH('')
) AS Daughters
*You could add a substring to get rid of the final , but in my case this wasn’t required with the C# string.Split method
I found the best way to understand was to give it a go with some data and provided you understand the way joins work you can manipulate it to suit your needs.
Naturally the above is just demo data etc. in my actual implementation I also needed to include a case statement as the second column was also populated, if not with a parent then by itself, so the data had to be cleaned up even more.
I learnt something doing this hope it helps.