List:General Discussion« Previous MessageNext Message »
From:Garaud Jean-Claude Date:July 13 2006 2:01pm
Subject:Re: Comparison problem
View as plain text  
On Thursday 13 July 2006 14:19, Peter Brawley wrote:
> 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
>
I agree that LEFT JOIN is probably part of the solution. But the command you 
suggest do not work, perhaps because several Chimeric_Cluster_IDs and 
Unigene_ID are void or NULL. I want a list of AC from  Chimeric_Cluster_IDs 
that are not found in the gene_length table (Unigene_ID). It would perhaps be 
necessary to extract each AC from Chimeric_Cluster_IDs and test each against 
gene_length.Unigene_ID, but I really don't know how to do it.

> >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.
>
Thank you, found :
http://dev.mysql.com/doc/refman/5.0/en/control-flow-functions.html

Jean-Claude

> PB
>
> -----
>
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