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>

[Dan_Bracuk]

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.

[witom_iam]

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?

[Dan_Bracuk]

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

[witom_iam]

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