Search Mailing List Archives


Limit search to: Subject & Body Subject Author
Sort by: Reverse Sort
Limit to: All This Week Last Week This Month Last Month
Select Date Range     through    

[Go-database] Question about tree structure occurrences

Chris Stubben stubben at lanl.gov
Fri Nov 25 10:57:55 PST 2005


Hi,

If you have mysql 4.1+, you could use GROUP_CONCAT and list all parents 
with ONLY 2 children first and then search for your structures.


CREATE TABLE 2child AS
select term1_id as parent, group_concat(term2_id order by term2_id) as 
children
from term2term group by term1_id having count(*)=2;
--2135 rows

This query will find the first diagram (7 cases).

select children, count(*) from 2child group by 1 having count(*)=2;

Or use a self join to list parent ids as well.

select a.parent, b.parent, a.children
from 2child a,
2child b
where a.children=b.children
and a.parent<b.parent
order by 1;

+--------+--------+-------------+
| parent | parent | children    |
+--------+--------+-------------+
|   3122 |   5828 | 3366,10530  |
|   6094 |  17503 | 6118,6119   |
|  12149 |  12454 | 12455,12456 |
|  14372 |  15838 | 5232,15798  |
|  17491 |  17492 | 17493,17494 |
|  17853 |  17857 | 17849,17851 |
|  17854 |  17856 | 17848,17850 |
+--------+--------+-------------+


Use substring_index functions to find the second diagram (one case)

select *
from 2child a,
2child b,
2child c
where substring_index(a.children,',',1)=substring_index(b.children,',',1)
   AND substring_index(a.children,',',-1)=substring_index(c.children,',',1)
   AND 
substring_index(c.children,',',-1)=substring_index(b.children,',',-1);

+--------+------------+--------+------------+--------+-------------+
| parent | children   | parent | children   | parent | children    |
+--------+------------+--------+------------+--------+-------------+
|  14631 | 2875,14632 |  14633 | 2875,14634 |  14624 | 14632,14634 |
+--------+------------+--------+------------+--------+-------------+


Chris.

ps.  I'm using the database downloaded Oct 9.





chris mungall wrote:
> Hi Alberto
> 
> For 1, yes, it is possible, and such structures do exist in GO. Try this:
> 
> SELECT DISTINCT
>  x.term1_id,
>  y.term1_id,
>  x.term2_id,
>  y.term2_id
> FROM
>  term2term AS x,               -- vertical, left
>  term2term AS y,               -- vertical, right
>  term2term AS a,               -- diagonal, lower left to upper right
>  term2term AS b                -- diagonal, lower right to upper left
> WHERE
>  x.term1_id = b.term1_id       AND
>  y.term1_id = a.term1_id       AND
> 
>  x.term2_id = a.term2_id       AND
>  y.term2_id = b.term2_id       AND
> 
> x.term1_id != y.term1_id       AND          -- change to < to avoid 
> reporting symmetries
> x.term2_id != y.term2_id;
> 
> I'll leave it to you to extrapolate this to 2
> 
> On Nov 25, 2005, at 9:10 AM, Albert Vilella wrote:
> 
>> Dears,
>>
>> I would like to ask a question regarding the structure of Gene Ontology:
>>
>> Is it possible, at some point of the tree, to have parent-child
>> relationships like the ones depicted in the diagram?
>>
>> (childs at the bottom, parents at the top)
>>
>> http://161.116.70.61/Diagram1.png
>>
>> Thanks in advance,
>>
>> Bests,
>>
>>     Albert.
>>
>>
>> _______________________________________________
>> Go-database mailing list
>> Go-database at mail.fruitfly.org
>> http://mail.fruitfly.org/mailman/listinfo/go-database
> 
> 
> _______________________________________________
> Go-database mailing list
> Go-database at mail.fruitfly.org
> http://mail.fruitfly.org/mailman/listinfo/go-database
> 


-- 
-----------------
Chris Stubben

Los Alamos National Lab
BioScience Division
MS M888
Los Alamos, NM 87545

Phone: (505) 667-3295

--
This message is from the GOFriends moderated mailing list.  A list of public
announcements and discussion of the Gene Ontology (GO) project.
Problems with the list?           E-mail: owner-gofriends at geneontology.org
Subscribing   send   "subscribe"   to   gofriends-request at geneontology.org
Unsubscribing send   "unsubscribe"  to  gofriends-request at geneontology.org
Web:          http://www.geneontology.org/



More information about the go-friends mailing list