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

x

join two foreign keys to one primary key in other table

Sukhwinder Singh
on
Medium Priority
401 Views
Last Modified: 2006-11-18
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


Comment
Watch Question

That should work.

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


Author

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

Author

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

OR

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.