Jean-Claude
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';
Any idea ?
That is called an exclusion join. To get at it, you need to adopt
explicit join syntax instead of comma join syntax:
SELECT
Chr_Name, Chimeric_Cluster_IDs, Unigene_ID
FROM 150genes AS g
LEFT JOIN gene_length AS l
ON (Chimeric_Cluster_IDs LIKE concat('%', Unigene_ID, '|%')
OR Chimeric_Cluster_IDs LIKE concat('%', Unigene_ID, 'M%')
OR Chimeric_Cluster_IDs LIKE concat('%', Unigene_ID)
)
WHERE l.unigene_id IS NULL
ORDER BY Chr_Name+0
LIMIT 0,2
A last question : are there structures like if...then,
for...next, while etc. in MySQL ?
IF ... THEN and CASE .. are available within SELECT arguments. Control
flow constructs like FOR... and WHILE... are available only in stored
routines.
PB
-----
Garaud Jean-Claude wrote:
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
--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.394 / Virus Database: 268.9.10/386 - Release Date: 7/12/2006
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
