none
sub query using multiple tables RRS feed

  • Question

  • I am trying to get back a set of records from one table based on the return values of two other tables.  The way I have my queries works fine but it seems quit ugly and klugie.    

    The first query returns a set of records from two tables, on average 6.  From here I go through a foreach loop getting the id from the query and use it to :

    1: get the numbers of questions pertaining to the question id

    2: get the number of questions that were answered

    Is there a better way of doing this?  I have posted my code below, any help would be welcomed.

     

     

    Thanks Bill O.

     

    class csPercentages

        {

     

           

            public static double Get_ScenarioQuestions_Percentages(string sCategoriID)

            {

                int cnt = 0;

                double numPosible = 0.00, numAnswered = 0.00, completed = 0.00;

     

                IEnumerable<DataRow> Query = (from primST in csGlobalData.primaryScenarioTable.AsEnumerable()

                                              where primST.Field<string>("PARENT_ID") == sCategoriID.ToString()

                                              select primST)

                                              .Union(from detailST in csGlobalData.detailedScenarioTable.AsEnumerable()

                                                     where detailST.Field<string>("PARENT_ID") == sCategoriID.ToString()

                                                     select detailST);

     

                foreach (DataRow dr in Query)

                {

                    string pID = dr.Field<string>("QUESTION_ID").ToString();

     

                    cnt += (from scenQuestion in csGlobalData.modifiedResponseTable.AsEnumerable()

                            where scenQuestion.Field<string>("PARENT_ID") == pID

                            select scenQuestion).Count();

                    numAnswered += (from scenQuestion in csGlobalData.modifiedResponseTable.AsEnumerable()

                              where scenQuestion.Field<string>("PARENT_ID") == pID &&

                              !String.IsNullOrEmpty(scenQuestion.Field<string>("SCENARIO_SCORE").ToString())

                                    select scenQuestion).Count();

                }

     

                numPosible = cnt / 4; //there are 4 questions per liklihood and there are three liklihoods

                                        //but only one question can be answered per liklihood.

     

                completed =  (numAnswered / numPosible) * 100;

     

                return completed;

            }

        }

    Monday, December 14, 2009 8:10 PM