Results 1 to 9 of 9
  1. #1
    Join Date
    Jun 2007
    Posts
    5

    Red face Data table design question

    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!

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    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

  3. #3
    Join Date
    Jun 2007
    Posts
    5
    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!

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    it's simple -- you need a third table for the number-group relationship

    do a search on many-to-many relationship or association table
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jun 2007
    Posts
    5
    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!

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Jun 2007
    Posts
    5
    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.

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    ah, i get it
    Code:
    select t.TeamName
      from Team as t
    inner
      join (
           select a.TeamID
             from Member as m
           inner
             join Associator as a
               on a.MemberID = m.MemberID
           group
               by a.TeamID
           having count(
                   case when m.MemberName 
                            in ('John','Mary')
                        then 'ok' end
                       ) = 2
              and count(
                   case when m.MemberName 
                        not in ('John','Mary')
                        then 'not ok' end
                       ) = 0
           ) as d
        on d.TeamID = t.TeamID
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Jun 2007
    Posts
    5

    Smile

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •