From: Peter Brawley
Date: July 13 2006 2:52pm
Subject: Re: Comparison problem
List-Archive: http://lists.mysql.com/mysql/199905
Message-Id: <44B65E33.9000007@earthlink.net>
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="=======AVGMAIL-44B65E33496A======="
--=======AVGMAIL-44B65E33496A=======
Content-Type: multipart/alternative; boundary=------------070103050105080604090200
--------------070103050105080604090200
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
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
>>
>> -----
>>
>>
>
>
--------------070103050105080604090200
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
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
-----
--------------070103050105080604090200--
--=======AVGMAIL-44B65E33496A=======
Content-Type: text/plain; x-avg=cert; charset=us-ascii
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline
Content-Description: "AVG certification"
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
--=======AVGMAIL-44B65E33496A=======--