From: Peter Brawley
Date: July 13 2006 12:19pm
Subject: Re: Comparison problem
List-Archive: http://lists.mysql.com/mysql/199895
Message-Id: <44B63A3E.7010300@earthlink.net>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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