SQL Add Processed Ids To A Single Cell Seperated With A Comma

i have the following sql query to get an idea of what it does please read the description below select catalogid, numitems, allitems - numitems ignoreditems from ( select

Solution 1:

My Linq-to-Sql answer to your previous question extended to this one (LINQpad query):

Dim odateRequired = {"MasterCard", "Visa"}
Dim odateNotRequired = {"Paypal", "Sofort"}

Dim result = From o In Orders Join i In Oitems On o.orderid Equals i.orderid _
             Let check = Not (From b In Bookeds Where b.ignoredoid=i.orderid).Any _
                     AndAlso o.ocardtype IsNot Nothing _
                     AndAlso ((odateRequired.Contains(o.ocardtype) AndAlso o.odate IsNot Nothing) _
                        OrElse odateNotRequired.Contains(o.ocardtype)) _
         Group By i.catalogid Into _
         numitems = Sum(If(check, i.numitems, 0)), _
         ignoreditems = Sum(If(check, 0, i.numitems)), _
         group
         Select catalogid, numitems, ignoreditems, _
             orderidcollection = String.Join(",", (From g in group Where g.check Select g.i.orderid))
result.Dump

Note this solution issues multiple SQL queries to determine the orderids for each catalogid where check is True to accumulate each orderidcollection. There may be a more efficient solution (either using some, probably convoluted, Linq-to-Sql that causes the generated SQL to do the equivalent of String.Join, or get the catalogid, numitems, check, orderid using Linq-to-Sql and then use Linq-to-objects to do the final accumulations).

Note also this query includes your Booked table (with LINQpad's pluralisation).