How so? Is there something you didn't understand? Peter's solution is the
right idea. You need to join the groups table to the users table once to
get the creatorname and again to get the ownername. Maybe it will be
clearer if we rewrite the query to make the join conditions explicit:
SELECT g.id, g.name, o.name AS 'owner', c.name AS 'creator'
FROM groups g
JOIN users o ON groups.owner = o.uid
JOIN users c ON groups.creator = c.uid;
Does that help?
Kris wrote:
No offense but your response has created more confusion about this..
Here is a more simple diagram for what I'd like to get from an SQL query:
Table users:
uid username
1 john
2 jim
3 mary
Table groups:
id name creator owner
1 test 1 1
2 abc 1 2
3 test2 2 3
output from mysql:
id(from groups) name(from groups) creatorname ownername
1 test john
john
2 abc john
jim
3 test2 jim
mary
I just want the output from the groups table but a name instead of the
number where creator# and owner# in groups table is associated to the
uid in users.
Hope this helps understand my problem..
Thanks
Kris
Peter Valdemar Mørch wrote:
Kris zoob-at-doomstar.com |Lists| wrote:
I am trying to join to tables:
users:
uid name
1 john
2 jim
3 mary
groups:
groupid groupname groupowner groupcreator
1 test1 1 1
2 test2 1 2
3 test3 2 3
My desired output would look like:
groupid groupname owner creator
1 test1 john john
2 test2 john jim
3 test3 jim mary
I was unable to find an example online.. Can anyone help?
try something like :
select groupid, groupname, owner.name as f_owner, creator.name as
f_creator from groups, users as owner, users as creator where
groups.groupowner = owner.uid and groups.groupcreator = creator.uid;
(untested - but principle should be sound...)
Peter
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
