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?

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`

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... =(((

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.

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

On 15 mar, 11:15, "rics" <ricardo.ce...@gmail.com> wrote:
> 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



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.