Re: query help-multiple joins

2006-05-23 Thread mel list_php 


Perfect!

I tried aliasing the field names but didn't think about the table, and was 
just stuck looking at that query without any idea...


Thanks a lot for your help.
melanie


From: Johan Höök <[EMAIL PROTECTED]>
To: mel list_php <[EMAIL PROTECTED]>
CC: mysql@lists.mysql.com
Subject: Re: query help-multiple joins
Date: Tue, 23 May 2006 13:16:33 +0200

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]


_
The new MSN Search Toolbar now includes Desktop search! 
http://join.msn.com/toolbar/overview



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: query help-multiple joins

2006-05-23 Thread Johan Höök 

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]

query help-multiple joins

2006-05-23 Thread mel list_php 

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]