none
Double Left Outer Join RRS feed

  • Question

  • I have three tables: Requests, DepartmentRequests, RequestType.  I am trying to query the Requests table based on dynamic query strings that query each of the tables.

    A single request can have 0 or more department requests, and a single department request can have 0 or more request types.

    I am using the Dynamic Query library to build my queries.

    My first attempt was:
    From reqs In (context.Requests.Where(RequestFilterString())) _
                Join deptreqs In (context.DeptRequests.Where(DeptFilterString())) On reqs.RequestID Equals deptreqs.RequestID _
                Join rt In (context.RequestType.Where(TypeCodeFilterString())) On rt.DeptRequestID Equals deptreqs.DeptRequestID _
                Select reqs Order By reqs.RequestDate Descending

    Both of the joins are inner joins, which means that only requests that have dept requests which have type codes will be returned by this query.

    After that I came up with this, which isn't quite all the way there:
            From reqs In (context.Requests.Where(RequestFilterString())) _
                Group Join deptreqs In (context.DeptRequests.Where(DeptFilterString())) On reqs.RequestID Equals deptreqs.RequestID Into Group _
                Select reqs Order By reqs.RequestDate Descending

    This will bring back all requests whether they have a dept request or not, but does not allow for searching by dept request type code.

    My attempts to add a second Group Join failed because I could not setup the On field1 Equals Field2 part, since the fields I wanted to match were from deptreqs and not from reqs.

    Any help on this would be greatly appreciated.

    --Peter
    Tuesday, February 17, 2009 3:49 PM