Professional Web Applications Themes

Question about select statement - MySQL

Hello, I have 3 tables. * users -- userid -- username * groups -- groupid -- groupname * users_groups -- usergroupid -- userid -- groupid I wish to do a select that returns all the content of the *users_groups* but with usernames and groupnames in place of userid and groupid. My actual select only return 1 record per user. This is the code: select u.userid, u.username, g.groupname from users u, groups g, users_groups ug where u.userid = ug.userid and g.groupid = ug.groupid How can I get all the records in *users_groups* ? Any thougths?...

  1. #1

    Default Question about select statement

    Hello,

    I have 3 tables.

    * users
    -- userid
    -- username

    * groups
    -- groupid
    -- groupname

    * users_groups
    -- usergroupid
    -- userid
    -- groupid

    I wish to do a select that returns all the content of the
    *users_groups* but with usernames and groupnames in place of userid
    and groupid.

    My actual select only return 1 record per user. This is the code:

    select u.userid, u.username, g.groupname
    from users u, groups g, users_groups ug
    where u.userid = ug.userid and g.groupid = ug.groupid

    How can I get all the records in *users_groups* ?

    Any thougths?

    rics Guest

  2. #2

    Default Re: Question about select statement

    On 15 Mar, 13:32, "rics" <com> wrote: 

    SELECT
    `ug`.`usergroupid`,
    `g`.`groupname`,
    `u`.`username`
    FROM `users_groups` `ug`
    JOIN `users` `u` ON `ug`.`userid` = `u`.`userid`
    JOIN `groups` `g` ON `ug`.`groupid` = `g`.`groupid`

    Captain Guest

  3. #3

    Default Re: Question about select statement

    On 15 mar, 10:44, "Captain Paralytic" <com> wrote: 









    >
    > SELECT
    > `ug`.`usergroupid`,
    > `g`.`groupname`,
    > `u`.`username`
    > FROM `users_groups` `ug`
    > JOIN `users` `u` ON `ug`.`userid` = `u`.`userid`
    > JOIN `groups` `g` ON `ug`.`groupid` = `g`.`groupid`[/ref]


    This returned only 3 records, that is the number of users in the table
    users. But in tabel users_groups I have 5 records... =((( This was
    basicaly the same statement I wrote earlier... =(((

    rics Guest

  4. #4

    Default Re: Question about select statement

    On 15 Mar, 13:56, "rics" <com> wrote: 
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]

    >
    > This returned only 3 records, that is the number of users in the table
    > users. But in tabel users_groups I have 5 records... =((( This was
    > basicaly the same statement I wrote earlier... =(((- Hide quoted text -
    >
    > - Show quoted text -[/ref]

    Please post the data in each table.

    Captain Guest

  5. #5

    Default Re: Question about select statement

    On 15 mar, 10:58, "Captain Paralytic" <com> wrote: 
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]


    >
    > Please post the data in each table.[/ref]



    Its only examples of data... I'm running it localy.

    USERS
    -----------------------------
    1 user1
    2 user2
    3 user3

    GROUPS
    -----------------------------
    1 group1
    2 group2
    3 group3

    USERS_GROUPS
    -----------------------------
    1 1 2
    2 2 3
    3 3 1
    4 2 1
    5 1 3

    And the 2 queries in this discussion are returning the same results.

    RESULTS
    -----------------------------
    1 user1 group2
    3 user3 group1
    2 user2 group1

    rics Guest

  6. #6

    Default Re: Question about select statement

    On 15 mar, 11:15, "rics" <com> wrote: 
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]

    >
    > Its only examples of data... I'm running it localy.
    >
    > USERS
    > -----------------------------
    > 1 user1
    > 2 user2
    > 3 user3
    >
    > GROUPS
    > -----------------------------
    > 1 group1
    > 2 group2
    > 3 group3
    >
    > USERS_GROUPS
    > -----------------------------
    > 1 1 2
    > 2 2 3
    > 3 3 1
    > 4 2 1
    > 5 1 3
    >
    > And the 2 queries in this discussion are returning the same results.
    >
    > RESULTS
    > -----------------------------
    > 1 user1 group2
    > 3 user3 group1
    > 2 user2 group1[/ref]



    Oh man, what a shame!!!!!!!!!!!!!

    The select was correct all the time. The problem was in the *groups*
    table. It was missing the third record for some reason... Sorry!

    =(((

    When I put the third record in the table the select returns the
    desired results.

    MEA CULPA!

    Thanks a lot for the help.

    rics Guest

Similar Threads

  1. Replies: 4
    Last Post: November 27th, 03:19 AM
  2. help with SQL SELECT statement
    By TimNMtl in forum Dreamweaver AppDev
    Replies: 12
    Last Post: August 21st, 08:28 PM
  3. Need help with select statement
    By Don in forum Microsoft SQL / MS SQL Server
    Replies: 2
    Last Post: July 23rd, 04:53 AM
  4. possible to run a select with an if statement in it?
    By Jim in forum Microsoft SQL / MS SQL Server
    Replies: 2
    Last Post: July 17th, 04:48 PM
  5. Select Statement Question (Again)
    By Largo SQL Tools in forum Microsoft SQL / MS SQL Server
    Replies: 9
    Last Post: July 14th, 05:02 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139