• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 385
  • Last Modified:

join two foreign keys to one primary key in other table

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

Sukhwinder Singh
Sukhwinder Singh
  • 3
  • 2
1 Solution
That should work.

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

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;

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

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now