I think he gave you the right answer.
you can also use joins instead of where, but it is the same idea, not sure which one of the two will be faster though:
select groupid, groupname, owner.name as f_owner, creator.name as f_creator from groups left join users as owner on owner.uid = groups.groupowner left join users as creator on groups.groupcreator = creator.uid
----- Original Message ----- From: "Kris" <[EMAIL PROTECTED]>
To: "Peter Valdemar Mørch" <[EMAIL PROTECTED]>
Cc: "Kris zoob-at-doomstar.com |Lists|" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>
Sent: Tuesday, November 30, 2004 7:08 PM
Subject: Re: help with SQL (join?) query
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]
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
