List:General Discussion« Previous MessageNext Message »
From:Peter Brawley Date:July 13 2006 2:52pm
Subject:Re: Comparison problem
View as plain text  
Jean-Claude

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

If there are rows with NULL keys in the gene_length table, and if you 
cannot fix that problem, you will need the NOT EXISTS(...) version of 
that query (examples at http://www.artfulsoftware.com/queries.php#29), 
something like ...

SELECT ...
FROM 150genes AS g
WHERE NOT EXISTS( 
  SELECT chr_name 
  FROM gene_length AS l
  WHERE g.Chimeric_Cluster_IDs LIKE CONCAT('%', l.Unigene_ID, '|%')
   OR g.Chimeric_Cluster_IDs LIKE CONCAT('%', l.Unigene_ID, 'M%')
   OR g.Chimeric_Cluster_IDs LIKE CONCAT('%', l.Unigene_ID)
)
ORDER BY ...

but be prepared for slowness. If your MySQL version is pre-4.1, or if 
the subquery version is too slow, move the subquery to a separate query, 
save it to a temp table (excluding the rows with NULLs), and join 
150genes to that.

All this will perform much better if you can restructure the tables to 
permit equality comparisons rather than require LIKE comparisons, which 
are extremely slow.

PB

-----

Garaud Jean-Claude wrote:
> 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
>>
>> -----
>>
>>     
>
>   

Attachment: [text/html]
No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.394 / Virus Database: 268.10.0/388 - Release Date: 7/13/2006
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