Hi,
I guess you should be able to do something like:
SELECT t1.term_id, t1.name, r.type_id, t2.term_id, t2.name
FROM term t1
LEFT JOIN relationTerm r ON r.term_id1 = t1.term_id
LEFT JOIN term t2 ON r.term_id2 = t2.term_id
/Johan
mel list_php skrev:
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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
