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
