How to compare two comma separated lists

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

How to compare two comma separated lists

Author

Topic

pyrrhus_finch
Yak Posting Veteran

51 Posts

Posted - 2005-03-06 : 13:32:51

Dear Friends,

I'm working on an incident reporting database for wherein lie the following relationships.

Users (teachers, administrators etc.) > Departments< Incidents
Some users will be assigned the same departments (one or many) as others, and an incident can have one or many departments assigned to them.

I was not sure how to lay this out so I started like this:
tblUsers
ID|USERNAME|LOGIN|PASS|DEPARTMENT
01|Joe Smithe|JSmith|skippe|MATH,ENGLISH,PE
02|Suzy Maey|SuzyM|mypas|ENGLISH
03|Mr.Principa|Princip|passs|

And the Incident table might look like:
tblIncidents
ID|INCIDENT|DEPARTMENT
01|Fistfightin|MATH,ENGLISH,PE
02|Spitting|PE

Then I say: Select * from tblIncidents Where [Department] IN (Select Department from tblUsers where ID = @varID)

you get the idea? It works so long as there is only one department listed in each field.

How do I compare two comma separated values to see if the same value appears in both?

Ok. Ok. I know this is bad design, but for the sake of learning - can anyone answer how to do it??? = )

Then: can anyone tell me how to re-design the data with an intemediary table for the departments and write a query to the same effect?

nr
SQLTeam MVY

12543 Posts

Posted - 2005-03-06 : 15:08:12

Very bad.

Users (User_id, UserName, Pass)
Users_Department (User_id, Department_id)
Department (Department_id, name)
Incident (Incident_id, description)
Incident_Department (Incident_id, Department_id)

For your query if you are trying to find the users which have a department involved in an incident then I don't think there is an easy way to do it without parsing the csv strings and turning them into the tables above.

maybe use
http://www.mindsdoor.net/SQLTsql/f_GetEntryDelimiitted.html

and

select user.* from users u, incidents i
where ',' + dbo.f_GetEntryDelimiitted(u.department, 1, ',', 'N' + ',' like '%,' + i.department + ',%'
and len(u.department) - len(replace(u.department, ',', '') > 0
union
select user.* from users u, incidents i
where ',' + dbo.f_GetEntryDelimiitted(u.department, 2, ',', 'N' + ',' like '%,' + i.department + ',%'
and len(u.department) - len(replace(u.department, ',', '') > 0
union
select user.* from users u, incidents i
where ',' + dbo.f_GetEntryDelimiitted(u.department, 3, ',', 'N' + ',' like '%,' + i.department + ',%'
and len(u.department) - len(replace(u.department, ',', '') > 1

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-03-06 : 15:10:33

Do a site search and forum search on "csv" and you'll get some ideas for the answer to your first question. You'll also get an appreciation of having a good db design so you can avoid dealing with this type of comparison.

Here is one possibility for your design as well as an sample query based on it.


Users
	UserID
	LastName
	FirstName
	MiddleName
	Login
	Password

Department
	DeptartmentID
	DeptartmentName

Incident
	IncidentID
	IncidentDesc

UserDepartment --association table for many to many
	UserID
	DepartmentID

IncidentDepartment --association table for many to many
	IncidentID
	DepartmentID


/* this query gets all incidents associated with a given user's department list*/
Select	u.UserName
	,d.DepartmentName
	,i.*
From	Users u

--this join gets all departments for given User
JOIN	UserDepartment ud
		ON u.userid = ud.userid
		AND Userid = @userid

--this join gets all incidents for given user's departments
JOIN	IncidentDeptartment idp
		ON ud.DepartmentID = idp.DepartmentID
JOIN	Incident i
		ON i.IncidentID = idp.IncidentID

--join just to report the Department names 
JOIN	Department d
		ON idp.DepartmentID = d.DepartmentID

Be One with the Optimizer
TG

pyrrhus_finch
Yak Posting Veteran

51 Posts

Posted - 2005-03-07 : 12:55:51

Thanks so much for the detailed response. I will work on it this afternoon and put the changes in place.

Really. Thanks a Million!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources