Comparing Lists with Individual Query Items??

[Fued10]

Hi guys I'm in urgent need of help.

I have a column in my table that collects lists(UserGroup), but I want to compare individual list items in Usergroup(Qry1) with individual list items in UserGroup(Qry2).

<CFQUERY NAME="Qry1" DATASOURCE="#db#">
SELECT UserID, UserGroup FROM users
WHERE usersid = #SESSION.userID#
</CFQUERY>

<CFQUERY NAME="Qry2" DATASOURCE="#db#">
SELECT UserID, UserGroup FROM users
WHERE usersgroup IN (#Qry1.usergroup#)
</CFQUERY>

What I am trying to do is compare individual list items in usergroup with each other thus why the sql IN function will not work.

Example
if Qry1 Usergroup equals 21, 15,17
and Qry2 Users/Usergroup:
John 13,15,12
Peter 20,19,14
Sarah 20,12,17

Then John and Sarah will be retrieved by Qry2

Pleasae, any help will be very much apreciated

[arrgh]

Your problem is the table design. You shouldn't store lists. That's why the query is so hard. Normalize the table and the query will be a simple join.

Unique users and groups should each have a separate table.

User Table: UserID (unique user ID)
GroupsTable: GroupID (unique group ID)

User assignments should go in a 3rd table. They keys being "UserID" and "GroupID"

UserID, GroupID
1 (John), 13 (Group A)
1 (John), 15 (Group B)
...

[Fued10]

Thanks for the response arrgh, unfortunately I cannot do this as I did not build the table and the table has been in use for years. Can anyone please think of a way around this.

Thanks in advance

[arrgh]

Gotcha. Sorry you're stuck with such a poorly structured table. MySQL might have other options. But for other db's try using LIKE. You'd have to loop through your list and use LIKE to check for each value. It's slow ... and frankly awful sql, but should work.

WHERE 1 = 0
<cfloop list="#Qry1.usergroup#" index="chkGroup">
    OR ','+ usersgroup +',' LIKE '%,#chkGroup#%'
</cfloop>

... like saying

WHERE  ','+ UserGroup +',' LIKE '%,21,%'
OR     ','+ UserGroup +',' LIKE '%,15,%'
OR     ','+ UserGroup +',' LIKE '%,17,%'

[arrgh]

Just noticed a missing comma

WHERE 1 = 0
<cfloop list="#Qry1.usergroup#" index="chkGroup">
    OR ','+ usersgroup +',' LIKE '%,#chkGroup#,%'
</cfloop>