List:General Discussion« Previous MessageNext Message »
From:Garaud Jean-Claude Date:July 18 2006 8:09am
Subject:Re: Comparison problem
View as plain text  
On Thursday 13 July 2006 16:52, Peter Brawley wrote:
> 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

My MySQL version is 5.0.4-beta. I tested your suggestion yesterday but I was 
unable to get a satisfactory result, apparently (at least) a problem with 
NULLs  "WHERE field IS NOT NULL" in subqueries which return NULL fields !! 
Anyway, thank you for your help. I will try again later (I am too busy doing 
other things now) and post the solution if I find one.

Jean-Claude

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