How to query submitted service requests, and their selected offering values

Version 1

    Details

    How to query submitted service requests, and their selected offering values


    Resolution

     

    The two key tables are dbo.ServiceReq (stores the Service Request data) and dbo.ServiceReqParam (contains the parameter names, and their respective values, per-request).

    The best way to demonstrate this is through a join:

    select ServiceReq.ServiceReqNumber, ServiceReq.ProfileFullName, ServiceReqParam.ParameterName, ServiceReqParam.ParameterValue
    from ServiceReq
    LEFT JOIN ServiceReqParam
    ON ServiceReq.RecID=ServiceReqParam.ParentLink_RecID
    where ServiceReq.ServiceReqNumber='10002'


    You can return more results by opening up your query:

    select ServiceReq.ServiceReqNumber, ServiceReq.ProfileFullName, ServiceReqParam.ParameterName, ServiceReqParam.ParameterValue
    from ServiceReq
    LEFT JOIN ServiceReqParam
    ON ServiceReq.RecID=ServiceReqParam.ParentLink_RecID
    ORDER BY Servicereq.ServiceReqNumber desc