We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you a podcast all about Citrix Workspace, moving to the cloud, and analytics & intelligence. Episode 2 coming soon!Listen Now


join two foreign keys to one primary key in other table

Sukhwinder Singh
Medium Priority
Last Modified: 2006-11-18
I have two tables messages and members.

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

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

Watch Question

That should work.

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


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;

sukh,sukh,2006-03-30 05:05:05,testing
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

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts


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.
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
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.


Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.