SQL Query Help

[clamum]

I have a table of users (just under 10,000 records) and a table of groups (about 5-6 records). I need to go through each user and determine which group(s) they should be placed in. This is decided by the data in several of the user's columns and the criteria for the group.

For example, a user has country "US" and job title "Manager". A group has criteria "Country" and values "US,CA,MX". Another group has criteria "JobTitle" and values "Owner,Administrator".

The script needs to check the values for the user's columns (Country and JobTitle), and if the values are contained in a group's values, the user is assigned to that group (record written to another table with the user's alias and group's ID). So in this case, the user gets placed in the first group but not the second.

I was thinking about using cursors to accomplish this since a lot of logic and checking is involved. Use one cursor to iterate through the users and another to iterate through the groups, for each user. This isn't exactly efficient, but the script is only run once a week and it does not need to be real fast. A run time of say 30 seconds is even acceptable.

Any thoughts on this? I'm no SQL expert so I'm not sure at all how I'd do this without cursors.

 

[brandonb]

A cursor would work. However, I think you can accomplish the task without using a cursor.

This is how I'd do it. I'd create 3 tables.

group
id description
1 our first group
2 2nd group

group_Country
group description
1 US
1 CA
1 MX

group_Jobtitle
group description
2 Owner
2 Administrator
null Manager

user
group country jobtitle username
null US Manager Joebob

Now you can scan through the user table and assign a group by doing this:

UPDATE user SET user.group = group_Country.group
FROM user
INNER JOIN group_Country ON user.country = group_Country.description

UPDATE user SET user.group = group_Jobtitle.group
FROM user
INNER JOIN group_Jobtitle ON user.jobtitle = group_Jobtitle.description


The reason why I'd do it this way is because you can make any country/jobtitle go to whatever group, and you don't have to parse a string of data to figure out whats going on (using substring or whatever the commands are), and you don't need a cursor its really only a few sql statements.

 

[BoberFett]

Use brandonb's method. Updates on joins can get a bit more complicated than just a simple update but they're very powerful and they're fast as lightning.