Re: Substract queries

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(at)postgresql(dot)org
[mailto:pgsql-sql-owner(at)postgresql(dot)org] On Behalf Of Craig Ringer
Sent: Thursday, May 22, 2008 9:05 PM
To: Nacef LABIDI
Cc: pgsql-sql(at)postgresql(dot)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