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@[EMAIL PROTECTED]
On Behalf Of Craig Ringer
Sent: Thursday, May 22, 2008 9:05 PM
To: Nacef LABIDI
Cc: pgsql-sql@[EMAIL PROTECTED]
Re: [SQL] Substract queries
Nacef LABIDI wrote:
> Hi all,
>=20
> 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=20
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=20
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=20
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 =3D 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
--=20
Sent via pgsql-sql mailing list (pgsql-sql@[EMAIL PROTECTED]
)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
--=20
Sent via pgsql-sql mailing list (pgsql-sql@[EMAIL PROTECTED]
)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
|
