List:General Discussion« Previous MessageNext Message »
From:Garaud Jean-Claude Date:July 13 2006 11:45am
Subject:Comparison problem
View as plain text  
Hello all

I just suscribed to this list.
I am a scientist working in Strasbourg (France) on problems related to gene 
expressions. 
I have a first question : I am comparing 2 tables, "gene-length" and 
"150genes".

mysql> describe 150genes;
+----------------------+--------------+------+-----+---------+-------+
| Field                | Type         | Null | Key | Default | Extra |
+----------------------+--------------+------+-----+---------+-------+
| Spot_Id              | varchar(8)   | YES  |     | NULL    |       |
| Bank_name            | varchar(8)   | YES  |     | NULL    |       |
-----
| Chimeric_Cluster_IDs | varchar(100) | YES  | MUL | NULL    |       |
+----------------------+--------------+------+-----+---------+-------+
13 rows in set (0.00 sec)

mysql> describe gene_length;
+------------+---------------------+------+-----+---------+-------+
| Field      | Type                | Null | Key | Default | Extra |
+------------+---------------------+------+-----+---------+-------+
| Chr_Name   | varchar(2)          | YES  |     | NULL    |       |
| Start      | bigint(20) unsigned | YES  |     | NULL    |       |
| End        | bigint(20) unsigned | YES  |     | NULL    |       |
| Band       | varchar(4)          | YES  |     | NULL    |       |
| Unigene_ID | varchar(16)         | YES  |     | NULL    |       |
+------------+---------------------+------+-----+---------+-------+
5 rows in set (0.06 sec)

The 150genes table has several fields, "Chimeric_Cluster_IDs" contains a ill 
formatted list of accession codes (AC) like "Mm.128512|Mm.371574Mm.128512". 
The 150genes table contains several informations such as chromosome names 
(field "Chr_name") with the correspnding AC (field "Unigene_ID").

To see which  AC listed in Chimeric_Cluster_IDs.150genes exist in 
Unigene_ID.gene_length and retrieve the correspondig chromosome name I use a 
command like :

mysql> Select Chr_Name, Chimeric_Cluster_IDs, Unigene_ID from 150genes, 
gene_length where Chimeric_Cluster_IDs like concat('%', Unigene_ID, '|%') or 
Chimeric_Cluster_IDs like concat('%', Unigene_ID, 'M%') or 
Chimeric_Cluster_IDs like concat('%', Unigene_ID) order by Chr_Name+0 limit 
0,2\G
*************************** 1. row ***************************
            Chr_Name: X
Chimeric_Cluster_IDs: Mm.128512|Mm.371574Mm.128512|Mm.371574Mm.128512|
Mm.371574Mm.128512|Mm.371574
          Unigene_ID: Mm.128512
*************************** 2. row ***************************
            Chr_Name: 1
Chimeric_Cluster_IDs: Mm.246952|Mm.30837
          Unigene_ID: Mm.246952
2 rows in set (2.50 sec)

But how to list all AC in Chimeric_Cluster_IDs _ABSENT_ from the gene_length 
table, this one for instance :
mysql> select Chr_Name, Unigene_ID from gene_length where 
Unigene_ID='Mm.371574';
Empty set (0.03 sec)

Any idea ?

A last question : are there structures like if...then, for...next, while etc. 
in MySQL ? 

Thank you in advance

Jean-Claude
Thread
Comparison problemGaraud Jean-Claude13 Jul
  • Re: Comparison problemPeter Brawley13 Jul
    • Re: Comparison problemGaraud Jean-Claude13 Jul
      • Re: Comparison problemPeter Brawley13 Jul
        • Re: Comparison problemGaraud Jean-Claude18 Jul