I have a problem that I'm not able to solve. I have the following
table set:

User:
------
UserId
FirstName
SurName
....
..

UserGroup:
------
UserId
GroupId

Group:
------
GroupId
Name
GroupTypeId

GroupType:
------
GroupTypeId
Name

In short. An user can belong to one or more groups, a group can
contain one or more users. A group is of a specified type, given by
GroupTypeId, like "Team", "Region" etc.
An alternative design is to keep groups of a specific type in it's own
table, but this isn't as flexible since then there must be a table
added everytime there is a new kind of groups.
What I want is the ability to ask a query producing a result like
this:

FirstName  SurName  Team  Region
--------------------------------------------------------
Bill       Smith    X3    South
..........
.......

The problem is to get the "Team" and "Region" in the same row. I
haven't manage to do this even if I splited the "Team" and "Region" in
their own tables.

Any help on this issue would be greatly appriciated.

Kind regards, Ola Theander

This is not hard, but it requires using the same table TWICE in the
query.  Lets start with a simplified case with only one group:

  SELECT U.name, Team = G.name
    FROM User U
         JOIN UserGroup UG on U.userid = UG.userid
         JOIN Group G on UG.groupid = G.groupid
         JOIN GroupType GT on G.grouptypeid = GT.grouptypeid
   WHERE GT.groupetypename = 'Team'

Now lets add the reference to 'Region' by using different aliases.

  SELECT U.name, Team = G1.name, Region = G2.name
    FROM User U
         JOIN UserGroup UG1 on U.userid = UG1.userid
         JOIN Group G1 on UG1.groupid = G1.groupid
         JOIN GroupType GT1 on G1.grouptypeid = GT1.grouptypeid
         JOIN UserGroup UG2 on U.userid = UG2.userid
         JOIN Group G1 on UG2.groupid = G2.groupid
         JOIN GroupType GT2 on G2.grouptypeid = GT2.grouptypeid
   WHERE GT1.groupetypename = 'Team'
     AND GT2.groupetypename = 'Region'

Roy

Thanks for your answer. There is just a slight problem here, and that
is that an user doesn't have to be in both a "Team" and a "Region",
i.e. an user could belong only to a team.

  SELECT U.name, Team = G1.name, Region = G2.name
    FROM User U
         LEFT OUTER JOIN UserGroup UG1 on U.userid = UG1.userid
         JOIN Group G1 on UG1.groupid = G1.groupid
         JOIN GroupType GT1 on G1.grouptypeid = GT1.grouptypeid
         LEFT OUTER JOIN UserGroup UG2 on U.userid = UG2.userid
         JOIN Group G1 on UG2.groupid = G2.groupid
         JOIN GroupType GT2 on G2.grouptypeid = GT2.grouptypeid
   WHERE GT1.groupetypename = 'Team'
     AND GT2.groupetypename = 'Region'

Roy