On 15 mar, 10:58, "Captain Paralytic" <paul_laut...@yahoo.com> wrote:
> On 15 Mar, 13:56, "rics" <ricardo.ce...@gmail.com> wrote:
>
>
>
> > On 15 mar, 10:44, "Captain Paralytic" <paul_laut...@yahoo.com> wrote:
>
> > > On 15 Mar, 13:32, "rics" <ricardo.ce...@gmail.com> wrote:
>
> > > > 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?
>
> > > 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`
>
> > 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 -
>
> Please post the data in each table.



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