* Leonardo Javier Belén
> Thanks but actually I'm looking for a way to return all the relations
> upwards and downwards of a term. no matter the position (so I need general
> query to do that. Any ideas?
>
> >0100 - Rose tree
> >0101 - pink rose tree
> >0102 - red rose tree
> >0103 - rococo rose tree
> >0200 - generic tree
...relations upwards and downwards... the upward relations is always exactly
one, to a 'parent', right? But what is a downward relation in this case? Did
you mean siblings?
Creating some test data:
USE test;
CREATE TABLE plants (
id char(4) not null primary key,
descr varchar(32) not null default '');
INSERT INTO plants VALUES
('0100','Rose tree'),
('0101','pink rose tree'),
('0102','red rose tree'),
('0103','rococo rose tree'),
('0200','generic tree');
Find a plant and its parent based on id:
SELECT pa.descr 'parent',p.*
FROM plants p
LEFT JOIN plants pa ON
pa.id = CONCAT(LEFT(p.id,2),'00')
WHERE p.id = '0102';
+-----------+------+---------------+
| parent | id | descr |
+-----------+------+---------------+
| Rose tree | 0102 | red rose tree |
+-----------+------+---------------+
1 row in set (0.00 sec)
Find a plant, its parent and its siblings:
SELECT pa.descr 'parent',p.descr 'plant name',si.id 'sib.id',si.descr
'sibling'
FROM plants p
LEFT JOIN plants pa ON
pa.id = CONCAT(LEFT(p.id,2),'00')
LEFT JOIN plants si ON
LEFT(si.id,2) = LEFT(p.id,2) AND
RIGHT(si.id,2) != RIGHT(p.id,2) AND
RIGHT(si.id,2) != '00'
WHERE p.id = '0102';
+-----------+---------------+--------+------------------+
| parent | plant name | sib.id | sibling |
+-----------+---------------+--------+------------------+
| Rose tree | red rose tree | 0101 | pink rose tree |
| Rose tree | red rose tree | 0103 | rococo rose tree |
+-----------+---------------+--------+------------------+
2 rows in set (0.00 sec)
There could be many siblings, which is why this last query will return many
rows, one for each sibling. The parent and plant name is the same for all
rows, only the sibling info is different.
--
Roger
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
