how to build a query to display -- to a logged in user -- a list of other users with UserRoleID = 5 -- who ALSO live in the same state as the logged in user?

In brief: How can I build a query to display a list of "Principal" Users (think: public school principals) who have a specific UserRoleID 5 -- but, this list of Principals is displayed ONLY to a logged in "Liaison" User with UserRoleID 3.

Also, the list of displayed Principals must contain only Principals who live in the same state as the logged-in Liaison User. =) This is the tricky part.

In detail:

1. Assume a logged-in user with a valid session.auth.UserID.

2. table tbl_NBPTS_Principals contains data about many users, including Principals and Liaisons

3. All users are assigned to a state of residence (variable "State" in the query below)

4. variable UserRoleID defines users by user role: Principals = UserRoleID 5; Liaisons = UserRoleID 3

5. UserRoleID is not the same as UserID -- each user has his or her own unique UserID, for database integrity; variable UserID is PK in table tbl_NBPTS_Principals

So far, my query is:

  <cfquery datasource="#ds#" name="GetUserList">
  SELECT UserID
     , FirstName
     , LastName
     , State
     , UserEmail
     , UserRoleID
     , SSMA_TimeStamp
     , DateRecordModified
     , NameofSchool
     , NameofDistrict
  FROM tbl_NBPTS_Principals
  WHERE UserID = <cfqueryparam cfsqltype="cf_sql_integer" value="#val(session.auth.UserID)#">
  </cfquery>

This query requests details (FirstName, LastName, etc) from tbl_NBPTS_Principals and displays them for the UserID of the logged in Liaison user. This first query works fine.

I think I need another query, that will request from tbl_NBPTS_Principals a list of users with UserRoleID = 5, who are associated with a certain state -- the SAME state as the logged in Liaison user!

So, to sum up: a liaison user logs in. This liaison user lives in the state of Illinois. I need to figure out a query to display a list of Principals (UserRoleID 5) from Illinois to this logged in liaison user.

I am baffled because this query below seems like it should work, but it does not:

  <!--- in this query, select columns from table tbl_NBPTS_Principals --->
  <!--- we want columns for only the logged-in UserID; UserRoleID = 5; and the State must match the State of the current logged-in user --->
  <!--- the data is selected for the logged in UserID, and displayed in the table below; logged-in user can edit this data --->

  <cfquery datasource="#ds#" name="GetPrincipalsbyState">
  SELECT UserID
     , FirstName
     , LastName
     , State
     , UserEmail
     , UserRoleID
     , SSMA_TimeStamp
     , DateRecordModified
     , NameofSchool
     , NameofDistrict
  FROM tbl_NBPTS_Principals
  WHERE UserRoleID = <cfqueryparam cfsqltype="cf_sql_integer" value="5">
    AND State = <cfqueryparam cfsqltype="cf_sql_varchar" value="#session.auth.State#">
  </cfquery>

... the results are blank. Where am I going wrong?

In application.cfc, variable session.auth.State is defined correctly in function onApplicationStart.

Thanks again for helping me. I've been staring at this all day. I really look forward to finding out how this works. =)

Eric