Hi ,The query is like this ,
Except
SELECT * from ((SELECT COUNT(id) FROM table1) Except (SELECT COUNT(id)
FROM table2))tmp
Regards,
Ram
-----Original Message-----
From: pgsql-sql-owner@postgresql.org
[mailto:pgsql-sql-owner@postgresql.org] On Behalf Of Craig Ringer
Sent: Thursday, May 22, 2008 9:05 PM
To: Nacef LABIDI
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] Substract queries
Nacef LABIDI wrote:
> Hi all,
>
> I was wondering how can I substract result between select queries. I
mean I
> want to issue a query that does this :
> (select * from mytable where condition1) - (select * from mytable
where
> condition2)
If the subqueries return single (scalar) results, you can just subtract
them directly:
SELECT (SELECT COUNT(id) FROM table1) - (SELECT COUNT(id) FROM table2)
However, I'm guessing you REALLY want to match the records up in two
tables and compare them.
In that case what you need to do is read this:
http://www.postgresql.org/docs/8.3/static/tutorial-join.html
and this:
http://www.postgresql.org/docs/8.3/static/queries.html
including this:
http://www.postgresql.org/docs/8.3/static/queries-table-expressions.html
#QUERIES-FROM
then use a JOIN to combine both tables, matching up corresponding
records in each by (eg) an id field, then subtracting the fields.
Say I have
tablea
----------
ida numa
----------
1 11
2 48
3 82
5 14
tableb
----------
idb numb
5 20
2 30
3 40
1 50
then if I execute:
SELECT ida, numa, numb, numa - numb AS sub
FROM tablea, tableb
WHERE tablea.ida = tableb.idb';
I'll get a result like:
ida numa numb sub
---------------------------
2 48 30 18
5 14 20 -6
3 82 40 42
1 11 50 -39
which is what I suspect you want. Note that the results do not appear in
any particular order.
If what you really want is a query that returns all records in the first
query EXCEPT those returned by the second query, then see:
http://www.postgresql.org/docs/8.3/static/queries-union.html
--
Craig Ringer
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
