Results 1 to 9 of 9
  1. #1
    Join Date
    Nov 2006
    Posts
    13

    Unanswered: Retrieve data from 3 tables which are related to each other

    Hey,

    I want retrieve data from 3 table my tables structure is this

    tblUsers

    U_ID - Name

    3 John


    tblGroups

    G_ID - Name

    5 Admins
    6 Moderators


    Now I want join some of the users to different groups for example John maby is a member of two groups (Admins and Moderators)

    in order to do this I created a new table names tblGroupsUsers

    tblGroupsUsers

    ID - User_ID - Group_ID

    1 3 5
    1 3 6


    its ok, but Now I don't know how to retrive my users list from database I don't know how to write a wuery for this
    I have tried this :

    Code:
    strSQL = "SELECT tblUsers.name, tblUsers.U_ID, tblGroups.G_ID, tblGroupsUsers.Group_ID, tblGroupsUsers.User_ID FROM tblUsers INNER JOIN tblGroupsUsers ON tblGroupsUsers.User_ID = tblUsers.U_ID, tblGroups WHERE tblGroupsUsers.Group_ID = tblGroups.G_ID ORDER BY tblUsers.name ASC;"
    Its working withut error but the problem is the results its like this


    John

    John


    its will retrive the username twice , I think its reading based on tblGroupsUsers table because it has two rows ,
    help please I need this how can I configure my query to get eache name once

    Thanks
    Last edited by devman; 05-28-07 at 17:46.

  2. #2
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    1. it's very weird to mix ANSI join syntax with that "other" join syntax. I would use ANSI all the way.

    2. if all you want is the groupid, there's no reason to join to tblGroup.

    3. if columns store the same data, they should be named the same. You should not have U_ID in one table and User_ID in another. Make them all User_ID. same issue with G_ID and Group_ID. I have fixed that in the query below.

    Code:
    SELECT 
      tblUsers.name
      ,tblUsers.User_ID
      ,tblGroupsUsers.Group_ID
    FROM tblUsers 
    INNER JOIN tblGroupsUsers ON tblGroupsUsers.User_ID = tblUsers.User_ID
    ORDER BY tblUsers.name ASC
    4. if a user is in two or more groups, you'll get two or more rows back. That makes perfect sense to me. what's wrong with that? Otherwise you'd have to return comma-delimited list or some such, which is not recommended.

    5. it's one of my pet peeves to see tables with the tbl prefix. Hungarian notation sucks. drop it.
    Last edited by jezemine; 05-28-07 at 19:38.

  3. #3
    Join Date
    Nov 2006
    Posts
    13
    Thank you buddy
    I just changes table column names and used your query but the same problem I got two rows back,

    So tell me how to fix this, let me explain for you what exactly I want
    As I told you a user might be member of two or more groups, I want system check if the user atleast is member of a group and them return its name, not if the user is member of for example 7 groups return 7 times the name of that memmber, to fix this problem it should read the members name base on tblUsers table not tblGroupsUsers ,

    But how can I fix this

  4. #4
    Join Date
    Nov 2006
    Posts
    13
    Thanks I found the solution

    strSQL = "SELECT DISTINCT tblUsers.U_Name FROM (tblGroupsUsers INNER JOIN tblUsers ON tblGroupsUsers.User_ID = tblUsers.U_ID) INNER JOIN tblGroups ON tblGroupsUsers.Group_ID = tblGroups.G_ID"

    the only way was DISTINCT

    anyway
    thanks for your reply

  5. #5
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    If all you need to do is to find all users that belong to a group then you don’t really need a join to the tblGroups
    And the select distinct … is similar to select … group by tblUsers.U_Name

    Try this instead (should be faster)

    Code:
    SELECT tblUsers.U_Name
    FROM   tblUsers
    WHERE  EXISTS (SELECT 1
                   FROM   tblGroupsUsers
                   WHERE  tblGroupsUsers.User_ID = tblUsers.U_ID)
    
    --or similar
    
    SELECT tblUsers.U_Name
    FROM   tblUsers
    WHERE  tblUsers.U_ID IN (SELECT tblGroupsUsers.User_ID
                             FROM   tblGroupsUsers
                             WHERE  tblGroupsUsers.User_ID = tblUsers.U_ID)

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Just picking up one one small thing - may have been a typo....
    Quote Originally Posted by devman
    ID - User_ID - Group_ID

    1 3 5
    1 3 6
    I'm assuming that ID is your PK and shouldn't have a duplicate value?

    Also, it may be worth dropping the ID as your primary key and having a combined key consisting of User_ID and Group_ID.
    George
    Home | Blog

  7. #7
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    ID should not exist at all, as george says. drop ID and make a composite key on UserID and GroupID. Otherwise your design will allow duped relationships.

  8. #8
    Join Date
    Nov 2006
    Posts
    13
    thanks
    but that ID is my typing mistake , sorry
    the second one is 2

  9. #9
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    even so the ID column shouldn't exist at all. the PK should be the combination of GroupID and UserID.

Posting Permissions

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