Help with CFQuery for comma delimited values and matching data.

Anyone have any suggestions how to loop thru a comma delimited list and get the value that is equal to the userID (the doctor to whom that support staff was assigned)?

I have three SQLServer tables:

The values within the surgeon table look like this:

ID (int) Surgeon_uniqname (nvarchar(50)
Surgeon_name (nvarchar(50)
SupportStaff (nvarchar(50)

values:

1 Spock Spock 1
2 Brooks Brooks 2,11
3 Greene Greene 4,6


The values within the SelectSupportStaff table look like this:

ID (int) supportStaff (nvarchar(150))
Lastname (nvarchar(50)
Deleted (nvarchar(50)

values:

1 Blue Blue 0
2 Green Green 0
3 Yellow Yellow 0
4 Red Red 0
5 Purplex Purplex 0
6 Brown Brown 0
11 Black Black 0


The values within the pref_SupportStaff table look like this:

ID (int) userID (nvarchar(50)) SupportStaff (nvarchar(50)

values:

1 Spock 1
2 Brooks 2,11
3 Greene 4,13



The results I wanted are on Details.cfm which should display a list that looks like this:

Uniqname /Lastname/SupportStaff /SupportStaffLastname
Doc1 / Spock / 1 / LastnameBlue
Doc2 / Brooks / 2,6 / LastnameGreen, LastnameYellow
Doc3 / Greene / 1,5 / LastnameRed, LastnameBlack


The problem is with the list of SupporStaff Lastname. It’s a comma delimited list so my query should sort that out. However, no matter what query I use, I only get the first value in the table

Here are the queries I’m using:

<CFQUERY NAME="getsurgeon" datasource = "#Application.DB#">
select *
from surgeon
</CFQUERY>

<!--- Get all staff --->
<cfquery name="getSupportStaff" datasource = "#Application.DB#">
SELECT *
from SelectSupportStaff
where deleted = '0'
</CFQUERY>
<!--- Get the which staff is assigned to which surgeon--->
<cfquery name="GetAssignments" datasource = "#Application.DB#">
Select SupportStaff
from surgeon
where ID = '#url.ID#'
</cfquery>

<!--- Get the staff preferences--->
<cfquery name="GetPref" datasource = "#Application.DB#">
Select *
from pref_SupportStaff
where id = '#getSupportStaff.ID#'
</cfquery>

<cfquery name="getstaff" datasource = "#Application.DB#">
select
ss.ID,
ps.id,
lastname,
ps.supportstaff
from Pref_supportStaff ps
inner join selectsupportstaff ss
on ps.id = ss.id
</cfquery>

<cfset userID = "#url.ID#">

<cfloop index="item" list="#ValueList(Getstaff.ID, ",")#">
<cfif ListFind(Getstaff.userID, '#item#') GT 0>
<cfset supportstaff = True /> <!--- If the query results in an answer greater than zero, set the varaible as true --->
<cfbreak />
</cfif>
</cfloop>
OR THIS
<cfloop list="getstaff" index="1">
#lastname#
</cfloop>

Neither one of these loop queries return the support staff lastname associated with the Doctor they are assigned to. One of the queries returns everyone in the list and one returns the first entry in the SelectStaffSupport table.

Thank you in advance for any suggestions.

 

First mistake I was making was not selecting the userid in the query on the preceeding page. So I changed it using 'as'.

<CFQUERY NAME="getsurgeons" datasource = "#Application.DSN#">
Select
ID,
surgeon_uniqname as userid, (so now I have a value I can use on next pg)
surgeon_name,
doctor_nbr,
sec_uniqname,
sec2_uniqname
FROM surgeon
order by surgeon_name
</CFQUERY>

<CFQUERY NAME="getstaffinfo" datasource = "#Application.DB#">
SELECT *
FROM pref_supportstaff
where userID = '#url.userID#' (this returns the matching data from next tbl)
</cfquery>



<td colspan="2" align="left" style="font-weight:normal" nowrap>
<CFOUTPUT query="getstaffinfo">
<cfset bFirst=0>
<cfloop list = "#supportstaff#" index="i" delimiter=",">
<cfscript>id=listgetat(i,1,",");</cfscript>
<!---This query must NOT be named the same as the query that collects the names from the
lookup table--->
<cfquery dbtype="query" name="getstaffname">
select *
FROM getSupportStaff
WHERE ID = '#variables.id#'
</cfquery>


<CFIF bfirst eq 0> <CFELSE> , </CFIF>
#getstaffname.firstname# #getstaffname.lastname#
<CFSET bfirst = 1>
</cfloop>
</CFOUTPUT>

So finally I have the two values that I was looking for. Two people working for the same doctor!

 

Do yourself a favor and don't store delimited data. It violates normalization rules and is a bad design

Whenever you find yourself storing lists of data in a single column, you should normalize. If two tables have a many-to-many relationship, each combination should be stored as a separate record in a third table:

SurgeonID, SupportStaffID, etc...
2, 2
2, 11

... instead of

SurgeonID, SupportStaffIDList
2, "2,11"