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
