ColdFusion Lists (in SQL queries and listFind)

I have a list of items (variable name: unitList) I get from this:

<cfquery name="qry_getOrgs" datasource="#request.DSN#">
		SELECT distinct(tblPerson.personUnit) FROM tblActivityAuthors
		LEFT JOIN 
		tblPerson ON tblPerson.personID = tblActivityAuthors.personID 
		WHERE activityID = #activityID#
        ORDER BY personUnit 
		</cfquery>
        <cfparam name="unitList" default="">
        <!---<cfset unitList = listAppend(unitList, qry_getOrgs.personUnit & "<br>")>--->
      	<cfset unitList = listAppend(unitList, qry_getOrgs.personUnit, chr(10))>
		<cfset unitList = replace(unitList, chr(10), "<br>", "all")>

Open in new window


Then I query a group of people who need to be notified if their unit is within this list:

 
<cfquery name="qry_getQAEmails" datasource="#request.DSN#">
    	SELECT empl_id, roleOrgs, tblPerson_RTIDetail.personEmail as personEmail 
        FROM authdb_users 
        LEFT JOIN tblPerson_RTIDetail ON tblPerson_RTIDetail.personRTIID = authdb_users.empl_id 
        	WHERE userRoleID = '5'
  	</cfquery>

Open in new window


So somewhere on this line:

WHERE userRoleID = '5'

I need to say and roleOrgs is in the list unitList.

Hope this makes sense!
traportAsked:
Who is Participating?
 
traportConnect With a Mentor Author Commented:
aqx I agree with you. I think I'm back to the drawing board with how I store this information. I need a 1 to 1 match here. I'm going to close this out and reward you the points for sticking with me. THANK YOU.
0
 
_agx_Commented:
I don't think you can use the existing list because you'll have delimiter problems. But if you've got a CSV list of unit numbers like this:

<cfset unitList = "1,2,5,9,91,10,22">

Try

WHERE userRoleID IN
(

      <cfqueryparam value="#unitList#" cfsqltype="cf_sql_integer" list="True">
)
0
 
traportAuthor Commented:
Okay, thanks!

It is the way I've set up the list that is giving me problems. How would I set up the list from the query so I could use the IN statement bearing in mind that these units might have hyphens in them as well...

<cfquery name="qry_getOrgs" datasource="#request.DSN#">
            SELECT distinct(tblPerson.personUnit) FROM tblActivityAuthors
            LEFT JOIN
            tblPerson ON tblPerson.personID = tblActivityAuthors.personID
            WHERE activityID = #activityID#
        ORDER BY personUnit
</cfquery>

So it might be something like, IN(SSES-SCS-SRD,SSES-SCS-SRD,SSES-SCS-SRD)
0
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

 
_agx_Commented:
Hyphen's don't matter.
>> So it might be something like, IN(SSES-SCS-SRD,SSES-SCS-SRD,SSES-SCS-SRD)

If you mean the query results are:
         PersonUnit
         SSES-SCS-SRD
         SSES-SCS-SRD
         SSES-SCS-SRD

Use valueList to convert them into a single csv list

       <cfset unitList = valueList( yourQUeryName.PersonUnit)>

like this;

         SSES-SCS-SRD,SSES-SCS-SRD,SSES-SCS-SRD

Then use it in your query. You may need to change the data type to cf_sql_varchar

WHERE userRoleID IN
(

      <cfqueryparam value="#unitList#" cfsqltype="cf_sql_varchar" list="True">
)


0
 
_agx_Commented:
If you meant each record contains a list of values:

ie
         PersonUnit
         SSES-SCS-SRD, SSES-SCS-SRD
         SSES-SCS-SRD, SSES-SCS-SRD, SSES-SCS-SRD
         SSES-SCS-SRD
         SSES-SCS-SRD, SSES-SCS-SRD

ValueList will still work. But from a db perspective storing lists isn't a great idea.
0
 
traportAuthor Commented:
Sorry to keep asking more questions - what if I want to know if it is just LIKE or is contained within one of the items in the list?

WHERE roleOrgs LIKE <cfqueryparam value="#unitList#" cfsqltype="cf_sql_varchar" list="True">
or
LIKE <cfqueryparam value="%#unitList#%" cfsqltype="cf_sql_varchar" list="True">


doesn't work.
0
 
_agx_Commented:
No worries.  So your table IS storing lists?

I'd need to see full example of the values you're comparing.  For example, if

       unitList    =  xxx-yyyy, aaa-bbb

What's an example of the roleOrgs record value you're trying match?
0
 
traportAuthor Commented:
I think it's the hyphens that are screwing this up... I could have like SSES and it works fine. But SSES- and it breaks.
0
 
_agx_Commented:
Actually just show the raw table values, like this

Row, tblPerson.personUnit
#1,   aaaa
#2,   bbb-xx,yyy,ccc                  <== multiple values in a single column


Row, roleOrgs
#1,    ???
#2,    ???
0
 
traportAuthor Commented:
Okay...

A person's roleOrg might  = BHCJ,0200206312,0200232312,0200233312,0200234312,0200235312,0200236312,0200332312,0200237312,0200253312,0200253350,SPHERE,0200193312,0200195312,DHSSPR,0200141350,0200217312,0200219312,0200220312,0200222312,0200224312,0200224350,0200226312,0200312312,0200426312,PHE,0200227312,0200228312,0200229312,0200231312,0200200312,0200218312,PHB,0200230312,0200230350

and the personUnits I might be searching could be = SSES-SCS-SRD
0
 
traportAuthor Commented:
Or a roleOrg that would actually contain a match:

SCS,0200065312,CAHIT,0200408312,DRS,0200303312,0200093312,0200357312,0200367312,0200405312,0200406312,ESD,0200064312,RCD,0200092312,SRD,0200080312,0200341312,0200343312,0200344312,0200345312,0200346312,0200347312,0200348312,0200355312,0200356312

with personUnit = SSES-SCS-SRD
0
 
_agx_Commented:
Oh boy.. You mean both columns in both tables can contain multiple values?

Which db type are you using?
0
 
traportAuthor Commented:
Yes. BOTH. SQL 2005.
0
 
_agx_Connect With a Mentor Commented:
Honestly using a list to search a list .. in SQL... is a lot harder.  The difficulty you're having now is one of the prime reasons db experts recommend against storing lists like this.  

Anyway, how many records are you talking about? If it's only a few you might just bite the bullet and pull all records and use CF list functions.  Not a great solution. But it's going to be tough to find any elegant solutions that work with denormalized tables.  How comfortable are you with stored procedures or UDF's?
0
 
traportAuthor Commented:
Thanks.
0
 
_agx_Commented:
If you need any help w/the table design, feel free to open another question.  If you flag it under the CF zone, as well as MS SQL 2005, we'd be happy to help.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.