Tricky query question. 
Author Message
 Tricky query question.

Hello all!

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



Sat, 30 Dec 2000 03:00:00 GMT
 Tricky query question.

Ola,

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



Sat, 30 Dec 2000 03:00:00 GMT
 Tricky query question.

Quote:

>Ola,
>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

Hi 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.



Sun, 31 Dec 2000 03:00:00 GMT
 Tricky query question.
Ola,

Quote:
>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.

In that case we need outer joins:

  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



Sun, 31 Dec 2000 03:00:00 GMT
 
 [ 4 post ] 

 Relevant Pages 

1. Tricky query question

2. Tricky query question

3. A tricky query question...

4. Tricky query for me, should be simple for you

5. Tricky Query Problem

6. tricky CASE WHEN query...

7. A Tuff Tricky One - Please Help with Queries

8. tricky query (for me atleast)

9. Tricky "most recent sales" query

10. Tricky update query

11. tricky query

12. Tricky UPDATE / Select SQL query problem.


 
Powered by phpBB® Forum Software