join two foreign keys to one primary key in other table

I have two tables messages and members.

Messages
-----------
id | from_member_id | to_member_id | date | subject | body
1 | 1 | 2 | 2005-03-30 05:05:055 | test | testing it


Members
---------
id | name
1 | sukh
2 | dukh


Now messages.from_member_id and messages.to_member_id are foreing keys pointing to same  primary key column member.id

I want to know is it possible to get names of both members using one join.

following query cannot work I think as how dbms will know which member name is from and which is to.

select member.name as from_member, member.name as to_member, messages.date, messages.subject from members, messages where members.id=messages.from_member_id


Sukhwinder SinghAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

curlypinheadCommented:
That should work.

just use 'from_member' and 'to_member' when you access the returned data set.


0
Sukhwinder SinghAuthor Commented:
Doesn't work. Returns same name twice.

CREATE TABLE `test_members` (
  `id` tinyint(3) unsigned NOT NULL auto_increment,
  `name` varchar(30) default '',
  PRIMARY KEY  (`id`),
  UNIQUE KEY `id` (`id`)

) TYPE=MyISAM COMMENT='All members';

INSERT INTO test_members (id, name) VALUES (1, 'sukh');
INSERT INTO test_members (id, name) VALUES (2, 'dukh');

CREATE TABLE `test_messages` (
  `id` tinyint(3) unsigned NOT NULL auto_increment,
  `from_member_id` tinyint(3) unsigned default '0',
  `to_member_id` tinyint(3) unsigned default '0',
  `subject` varchar(40) default NULL,
  `date` datetime NOT NULL default '0000-00-00 00:00:00',
  PRIMARY KEY  (`id`),
UNIQUE KEY `id` (`id`)

) TYPE=MyISAM COMMENT='messages';

INSERT INTO test_messages (id, from_member_id, to_member_id, subject, date) VALUES (1, 1, 2, 'testing', '2006-03-30 05:05:05');


select test_members.name as from_member, test_members.name as to_member, test_messages.date, test_messages.subject from test_members, test_messages where test_members.id=test_messages.from_member_id;

Result:
------
from_member,to_member,date,subject
sukh,sukh,2006-03-30 05:05:05,testing
0
curlypinheadCommented:
oh yeah....ok

select fromMember.name as from_member, toMember.name as to_member, test_messages.date, test_messages.subject
from test_members as fromMember, test_members as toMember, test_messages where
toMember.ID = test_messages.to_member_id and fromMember.ID = test_messages.from_member_id

that will give you both names
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Sukhwinder SinghAuthor Commented:
yes thanks it works.

Will this type of join (self join?) make queries little more slow when there are 100 thousand or more messages.

I have a small question regarding mysql primary keys. If you can answer it please.

If is say primary key(id). does it mean that mysql will automatically create a unique index on it and I won't need to create either extra index or another unique index.

Thank you very much.
0
curlypinheadCommented:
it isn't really a matter of making it slower, it is necessary to do this type of operation.  it should be a fairly fast operation.

Primary keys are, by definition, indexed and unique
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.