[VPM] SQL statement

Darren Duncan darren at DarrenDuncan.net
Tue May 13 16:23:38 CDT 2003


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