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

-----