Member Avatar for 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

  • 2 Contributors
  • 4 Replies
  • 109 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by arrgh
Member Avatar for 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)
...

Member Avatar for 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

Member Avatar for 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,%'
Edited by arrgh because: n/a
Member Avatar for arrgh

Just noticed a missing comma

WHERE 1 = 0
<cfloop list="#Qry1.usergroup#" index="chkGroup">
    OR ','+ usersgroup +',' LIKE '%,#chkGroup#,%'
</cfloop>
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.