[VPM] SQL statement

[abez]

If mysql or posgresql are being used look into their outer join syntax.

abram

On Tue, 13 May 2003, Malcolm Dew-Jones wrote:

>
> as an outer join, oracle syntax
>
> 	SELECT query, qfrom, qto
> 	FROM blastnhsp , blastxhsp
> 	WHERE blastnhsp.primary_key = blastxhsp.primary_key(+)
> 	and blastxhsp.primary_key IS NULL;
>
> This joins the two tables on what ever columns are the ones that would
> match, but uses an outer join on the second table so that if they don't
> exist in the second table then the second set of columns are just null.
>
> If you then take just the set of records in which the second table columns
> are null then you have the records that where unique in the first table.
>
>
> You can also do this with a not exists, and search in table 2 for the
> record of table 1
>
> 	SELECT something
> 	FROM table1
> 	WHERE not exists (select *
> 			  from table2
> 			  where table2.columns = table1.columns
>                          );
> $0.02
>

-- 
abez ------------------------------------------
http://www.abez.ca/ Abram Hindle (abez at abez.ca)
------------------------------------------ abez