List:General Discussion« Previous MessageNext Message »
From:mel list_php Date:May 23 2006 10:49am
Subject:query help-multiple joins
View as plain text  
Hi!

I'm stuck with a join query....

2 tables, term and relation, the first one with definition of terms the 
second one with the relations between them.

CREATE TABLE `term` (
  `term_id` int(11) unsigned NOT NULL auto_increment,
  `name` varchar(250) default NULL,
) ENGINE=MyISAM DEFAULT CHARSET=utf8 ;
and
CREATE TABLE `relationTerm` (
  `relation_id` int(11) unsigned NOT NULL auto_increment,
  `term_id1` int(11) unsigned NOT NULL default '0',
  `term_id2` int(11) unsigned NOT NULL default '0',
  `type_id` int(11) NOT NULL default '0',
  PRIMARY KEY  (`relation_id`),
  UNIQUE KEY `uniqueRelation` (`term_id1`,`term_id2`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 ;


in the second table term_id1 and term_id2 are referencing term_id of the 
first table.

Example of data:

INSERT INTO `term` VALUES (1, 'A');
INSERT INTO `term` VALUES (2, 'B');

INSERT INTO `relationTerm` VALUES (1, 1, 2, 1);

I want to obtain:
A 1 B

I managed to obtain A 1 2, but I don't know how to replace the 2 with the 
actual name of the entry, which would be B.

My query is:
SELECT term_id, name, type_id, term_id2
FROM term
LEFT JOIN relationTerm ON term_id1 = term_id

I should be able to somehow join again on term_id2 but just don't see how to 
do it...

Thanks for any help,
melanie

_________________________________________________________________
Be the first to hear what's new at MSN - sign up to our free newsletters! 
http://www.msn.co.uk/newsletters

Thread
query help-multiple joinsmel list_php23 May
  • Re: query help-multiple joinsJohan Höök23 May
    • Re: query help-multiple joinsmel list_php23 May