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