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
