Skip to main content
W
witom_iam
Participant
December 22, 2012
Question

Hide/remove a name from a menu/list

  • December 22, 2012
  • 1 reply
  • 743 views

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>

    This topic has been closed for replies.

    1 reply

    D
    Dan_Bracuk
    Inspiring
    December 22, 2012

    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.

    W
    witom_iamAuthor
    Participant
    December 22, 2012

    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?

    D
    Dan_Bracuk
    Inspiring
    December 22, 2012

    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

    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices