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.

Any better way to do this?
Many thanks!

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.

-PatP

Hi Pat.

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 hope this makes things clearer.
Thanks!

it's simple -- you need a third table for the number-group relationship

do a search on many-to-many relationship or association table

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:

Member:
MemberID
MemberName

Team:
TeamID
TeamName

Associator:
TeamID
MemberID

Now I have some people, allocated thusly--
Team1:
John
Mary

Team2:
John
Mary
Bob

Team3:
John
Billy

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.

Thanks!

maybe i don't understand the problem, but it does indeed look like john and mary are on team 2 as well as team 1

You are correct. However, in my case, I need to return the name of the team where only John and Mary exist. I do not want to retrieve teams that have other members as well.

Thanks.

ah, i get it

Perfect! And I learned some new SQL bits as well--never used that 'case when' before. Thanks so much!