[VPM] SQL statement

Nathanael Kuipers nkuipers at uvic.ca
Tue May 13 16:32:35 CDT 2003


Thanks to all, particularly Darren, as the outer-join syntax he showed is the 
same as what my supervisor wrote down, as he happened to drop in after I sent 
the email to the pm...I should have mentioned that we are using postgres.  
Some of you would argue that I should have asked my supervisor first too...but 
I prefer to obscure my lack of knowledge from supervisors if possible. :)

Thanks again!

Nathanael

>===== Original Message From Darren Duncan <darren at DarrenDuncan.net> =====
>On Tue, 13 May 2003, Nathanael Kuipers wrote:
>> What I want to do is something like the following incorrect statement:
>> SELECT query, qfrom, qto FROM blastnhsp WHERE NOT blastxhsp.query;
>
>I think that how you would do this depends on what database you are using.
>First of all, an outer-join is the simplest thing that at least partly
>does what you want.  Someone else gave Oracle syntax, and I'll show you
>the MySQL syntax here:
>
>SELECT n.query, n.qfrom, n.qto
>FROM blastnhsp n LEFT JOIN blastxhsp x ON x.query = n.query
>
>Now, I'm not sure whether adding a 'WHERE x.query IS NULL' would have the
>desired effect or not (try experimenting), due to issues of timing,
>such as whether the 'where' is evaluated before or after the join
>condition (it would have to evaluate after) but it probably would.
>
>Alternately, if as you say both tables have the exact same columns, then
>the best solution is actually a rowset-minus (same syntax as
>rowset-union), like this:
>
>SELECT query, qfrom, qto FROM blastnhsp
>MINUS
>SELECT query, qfrom, qto FROM blastxhsp
>
>This would return all the rows from blastnhsp that aren't in blastxhsp.
>The above syntax works in Oracle and probably MySQL 4.0 (or 4.1, but not
>3.x).
>
>-- Darren Duncan




More information about the Victoria-pm mailing list