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
>
> -----
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
