4 Replies Latest reply on Jan 10, 2013 9:33 AM by witom_iam

    Hide/remove a name from a menu/list

    witom_iam

      Is there a way to hide or remove a student's name from a menu list once that student is inserted into a database table? The table that list the student's name is different from the table that record's the student's grade. The table that record's the grade has the column   pointing to the primary key of the student table. It's called "gra_stuid" as one of the columns.

       

      Here's my code below:

       

      <cfquery name="graadreport" datasource="master">

      SELECT graadreport.*, trim(CONCAT(name.fname,' ',name.xholy))AS student, trim(concat(ltfname,' ',ltlname)) as LT, verbs.* FROM name, graADREPORT, graadlt, verbs

       

      WHERE graLT = '#Session.user_id#'

      AND name.foiid = grafoiid

      AND verbs.verid = gracontact

      AND graweekbegin = '#form.graweekbegin#'

      AND graweekend ='#form.graweekend#'

      GROUP BY grafoiid

      ORDER BY student

      </cfquery>

       

       

      <span class="label_student">student</span>

      <cfselect name="grafoiid" class="box_student">

      <option></option>

      <cfoutput query="student">

      <option value="#student.foiid#">#student.student#</option>

      </cfoutput>

      </cfselect>

        • 1. Re: Hide/remove a name from a menu/list
          Dan Bracuk Level 5

          You can do it by adding something to the where clause of your query to either exclude the records you don't want or be more specific about the ones you do.

          • 2. Re: Hide/remove a name from a menu/list
            witom_iam Level 1

            My list doesn't show up whenever I use the (AND grafoiid is null) inside of my WHERE statement. My code is below...

             

            <cfquery name="student" datasource="master">

            SELECT name.foiid, gradelt, gradelt.graltid, CONCAT(name.fname,' ',name.xholy,' ',name.slave) AS student, grafoiid

            FROM gradereport

            LEFT JOIN  name  ON foiid = grafoiid

            LEFT JOIN gradelt ON graltid = ltid

            WHERE 0=0

             

            AND type = '1stG'

            AND gradelt = '#session.user_id#'

            AND ltid = '#session.user_id#'

            AND CITY = 'richmond'

            AND STATUS <> 'd'

            AND STATUS <> 'T'

            AND grafoiid is null

            AND Form4Complete = 'yes'

            GROUP BY student

            ORDER BY student

            </cfquery>

             

            If it replace the (AND grafoiid is null) with (AND grafoiid is not null), it will only list the students who are already in grade table. I would like to tweak this to only show the list of students who have not been inserted into the grade table yet. Am I missing something in my code?

            • 3. Re: Hide/remove a name from a menu/list
              Dan Bracuk Level 5

              You might be getting caught by this.  The following looks like a left join.

               

              select somefields

              from table1 left join table2 on table1.field1 = table2.field1

               

              where table2.field2 = something;

               

              However, because you referenced table2 in the where clause, the query behaves as an inner join.  There are a couple of ways to contend with this.  The first is like this:

               

              select somefields

              from table1 left join table2 on table1.field1 = table2.field1

              and table2.field2 = something;

               

              The second, which is more likely to work in your situation, is like this

               

              select *

              from

              (

              select table2.field2 f2, some_other_fields

              from table1 left join table2 on table1.field1 = table2.field1

              ) you_need_this_alias

               

              where f2 is null

              • 4. Re: Hide/remove a name from a menu/list
                witom_iam Level 1

                Dan That solution did work. Thank you for your insight and help.