StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>Well, you can always write a query that will return a JOIN across both tables:</p> <pre><code> SELECT Parent.ID, Parent.Field2, Parent.Field3, Child.ID, Child.Value2, Child.Value3 FROM Parent INNER JOIN (or: LEFT OUTER JOIN) Child ON Parent.ID = Child.ParentID </code></pre> <p>but in this case, you'll get a "flattened" rowset back, in which the values for the parents are repeated for each of their child elements, obviously (standard SQL join behavior).</p> <p>As has been pointed out in the comments, if you use the <code>INNER JOIN</code>, of course, you'll only get back parents and their child records - parents without children will be left out. If you want those, too (with their child columns set to NULL), use <code>LEFT OUTER JOIN</code> instead of <code>INNER JOIN</code>.</p> <p>You're second option would be to have an ADO.NET query that returns two results, basically - something like</p> <pre><code> SELECT Parent.ID, Parent.FIeld2, Parent.Field3 ; SELECT Child.ID, Child.ParentID, Child.Value2, Child.Value3; </code></pre> <p>but then you'll need to parse two result sets from the response, associate the child records with their respective parents, and do some more work.</p> <p>Your third option would be to create a "FOR XML" query in SQL Server that would return an XML document representation the hierarchy of Parents and Child records in a single XML document.</p> <pre><code>SELECT Parent.ID, Parent.Field2, Parent.Field3, (SELECT Child.ID, Child.Value2, Child.Value3 FROM Child WHERE ParentID = Parent.ID FOR XML AUTO, TYPE, ELEMENTS) FROM Parent FOR XML AUTO, ROOT('Root'), ELEMENTS </code></pre> <p>Whichever works best for you - take your pick!</p> <p>Marc</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload