May 13, 2004 at 7:41 am
hello everyone...
i'll get right to the problem... i have been unable to successfully create a query that will do the following:
select the groupID from the groups table, where my employeeID is within the fields listed under groups.groupmembers
heres a bit more detail to describe my scenario:
groups table:
groupID | groupName | groupMembers
the groupMembers field will have been populated from a MULTIPLE select dropdown list, and will contain various employee IDs (ex. 2, 4)
what i am trying to do is create a query that will pull all groupIDs from the groups table where my employeeID is among the groupMembers
so for example, if my employeeID is 4, and 4 is a member of group#2, and group#7, i want to grab those groupIDs...
here are a few of the unsuccessful queries that i've tried:
-----------------------------------------------------------
get_query = "select employees.employeeid, groups.groupmembers from employees INNER JOIN groups on employees.employeeid = groups.groupmembers"
get_query = "select employees.employeeid, groups.groupmembers from employees INNER JOIN groups on employees.employeeid = groups.groupmembers" where employees.employeeid IN (groups.groupmembers)"
-----------------------------------------------------------
thanks for any help you can offer...
May 14, 2004 at 12:49 am
I would reconsider the table design. If I understood the explanation correctly, the groupMembers field contains a comma-separated list of employee IDs. This design violates Codd's normalization rules. A better solution would be to introduce a separate detail table with group and employee id columns.
If it is really necessary to keep the initial design, then the condition should be
',' + groups.groupmembers + ',' like '%,' + convert(varchar, employees.employeeid) + ',%'
or something similar, that checks for substrings. But, this condition is not effective for large amounts of data , since it does not use indexes.
Regards,
Goce Smilevski.
May 14, 2004 at 6:10 am
Use this split function to return the comma delimited list of id's as a single column table.
http://www.sqlservercentral.com/scripts/contributions/850.asp
Then you can use EXISTS or IN to determine if the id you are looking at exists in the list.
The best solution is still to redesign the application so that you have three tables, an employees table, a groups table, and a cross reference table holding groupid to employeeid As suggested by Goce
May 15, 2004 at 4:22 am
Hi!
tried to simulate your problem with temporary tables and following is the code for that, probably it will help you. You need to use charindex for checking the employeeID's existance in the list of groupmembers and its always advisable to keep a terminating symbol for every value of employeeID in the list of groupmembers (I have used comma(,) ) for that purpose.
create table #groups ( groupID int , groupmembers varchar(10))
create table #employees ( employeeID int )
insert into #groups values (1, '1,2,3,')
insert into #groups values (2, '2,3,4,')
insert into #groups values (3, '3,4,')
insert into #groups values (4, '1,4,5,')
insert into #employees values (1)
insert into #employees values (2)
insert into #employees values (3)
insert into #employees values (4)
insert into #employees values (5)
select #employees.employeeid, #groups.groupmembers , #groups.groupID
from #employees , #groups
where charindex( convert(varchar, #employees.employeeid)+',' , #groups.groupmembers) > 0
order by #employees.employeeid
--and #employees.employeeid = 1
--Indu
Indu Jakhar
May 17, 2004 at 11:08 am
pattern matching (PATINDEX or LIKE) is required . . . when you want "4", for example, a value of "14" would match also with CHARINDEX. mssql doesn't have the strongest regular expressions support. fortunately, it does process disjunctions on a first come basis. assuming most of your values will be in the middle, the first PATINDEX should catch most matches and the other two will not be processed.
DECLARE @s-2 NVARCHAR(500),
@sVal NCHAR(1)
SET @sVal = 4
SELECT 'FOUND IT'
WHERE PATINDEX('%,' + @sVal + ',%','2,3,14,4') > 0
OR PATINDEX(@sVal + ',%','2,3,14,4') > 0
OR PATINDEX('%,' + @sVal,'2,3,14,4') > 0
p.s. keep in mind the above PATINDEX functions simply indicate if the "value pattern" is present . . . they don't necessarily return the position of the particular value. so, this example finds ",4" which is position seven (the comma delimiter).
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply