Hi. This seems simple, and yet I can think of no elegant way to accomplish this simple modeling. Basically, I have a set of numbers that together can make up a group. There could be a single number in the group, or many numbers in the group. A particular number may appear in multiple groups.
My system will present a set of numbers which I need to go through in order to determine which group is involved. I wanted to do something normalized where each of my numbers is in a record, and these number records relate to the group record or records that they fall in, but the logic doesn't seem to work.
All I can come up with instead is to create a single record for each group that has a column that concatenates all of its member numbers, perhaps separated by a special character, and then match on that.
Can you explain the problem in terms of the real-world problem that you are trying to solve (for example finding which service person has worked on a series of customers that are having the same problems after service)? I think I know what you want from your explanation, but I'm not nearly certain enough that I'm willing to try to answer your question until I understand it better.
You could liken it to people who work in teams. The Team Members may belong to one or many Teams. You are presented with a list of Team Member names that worked on Monday and you want to find out which Team (group) name it was that worked. The sizes of teams are not all ths same.
I had wanted to have one record in my Team Member table per member, and then relate those records to the appropriate Group or Groups.
I should have elaborated--I understand about the association table, but I don't seem to be able to construct a proper query using that model. For example, let's say I have Team Members and Teams, and I make some tables:
Now I have some people, allocated thusly--
How can I construct the query so that if I am presented with the names "John" and "Mary", I can retrieve only the TeamName for Team1? Anything I'm coming up with is getting me Team2 as well. Or if I restrict based on count to fix that problem, then if I change my search criteria to include people not on the same team such as John and Jim, I'll end up retrieving Team3.
from Team as t
from Member as m
join Associator as a
on a.MemberID = m.MemberID
case when m.MemberName
then 'ok' end
) = 2
case when m.MemberName
not in ('John','Mary')
then 'not ok' end
) = 0
) as d
on d.TeamID = t.TeamID