[SQL] Substract queries
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) Thanks to all Nacef
Re: [SQL] Substract queries
Probably you are looking for EXCEPT. SELECT * FROM Tbl1 WHERE a=1 EXCEPT SELECT * FROM tbl2 WHERE a=1 and b=1; http://www.postgresql.org/docs/8.3/interactive/sql-select.html Regards, *Robins Tharakan* -- Forwarded message -- From: Nacef LABIDI <[EMAIL PROTECTED]> Date: Thu, May 22, 2008 at 8:45 PM Subject: [SQL] Substract queries To: pgsql-sql@postgresql.org 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) Thanks to all Nacef
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 numanumbsub --- 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
Re: [SQL] Substract queries
Thanks to all the EXEPT keyword is what I was looking for On Thu, May 22, 2008 at 5:36 PM, Niklas Johansson <[EMAIL PROTECTED]> wrote: > > On 22 maj 2008, at 17.15, Nacef LABIDI wrote: > >> 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 you (as implied above) query the same table in both cases, just do: > > SELECT * FROM mytable WHERE condition1 AND NOT condition2 > > Otherwise, use EXCEPT: > > SELECT * FROM mytable1 WHERE condition1 > EXCEPT > SELECT * FROM mytable2 WHERE condition2 > > in which case both queries must return the same type of rows. > > > > > Sincerely, > > Niklas Johansson > > > >
Re: [SQL] Substract queries
On 22 maj 2008, at 17.15, Nacef LABIDI wrote: 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 you (as implied above) query the same table in both cases, just do: SELECT * FROM mytable WHERE condition1 AND NOT condition2 Otherwise, use EXCEPT: SELECT * FROM mytable1 WHERE condition1 EXCEPT SELECT * FROM mytable2 WHERE condition2 in which case both queries must return the same type of rows. Sincerely, Niklas Johansson -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Extremely Low performance with ODBC
Hi, I'm executing a query through psql ODBC which is taking around 2 minutes to complete. When I run it from PgAdmin it takes less than 3 seconds. The query itself has : . 15 inner joins (from just around 10 different tables - the other inner joins are using different aliases for the same tables) . Select statement returns 1 field. . the testing database is selecting only 1 record. Taking a look at the explain analyze report, I see they are both quite the same and tested the ODBC driver through Visual Foxpro and Vb.NET as well, taking both around 2 minutes to finish. Any idea ? Thanks, Sebastian
[SQL] Query question
Hi, I can use some help with the following query please. Given a couple of tables I want to do a JOIN like operation. Except that one of the columns might be null. create table T1 ( id serial, name varchar(20) ); create table T2 ( id serial, name varchar(20) ); create table T1_T2 ( id serial, t1_id integer not null , t2_id integer ); Now I'd like to show a list of records from T1_T2 but reference T1 and T2 for the names instead of IDs. But T1_T2.t2_id might be null select T1_T2.id, T1.name , T2.name from T1, T2, T1_T2 where T1_T2.t1_id = T1.id and T1_T2.t2_id = T2.id Basically since t2_id might be null, the condition will fail and the query will fail thanks Medi
Re: [SQL] Query question
On Thu, 22 May 2008, Medi Montaseri wrote: > Hi, > I can use some help with the following query please. > > Given a couple of tables I want to do a JOIN like operation. Except that one > of the columns might be null. > > create table T1 ( id serial, name varchar(20) ); > create table T2 ( id serial, name varchar(20) ); > create table T1_T2 ( id serial, t1_id integer not null , t2_id integer ); > > Now I'd like to show a list of records from T1_T2 but reference T1 and T2 > for the names instead of IDs. But T1_T2.t2_id might be null > > select T1_T2.id, T1.name , T2.name from T1, T2, T1_T2 > where T1_T2.t1_id = T1.id and T1_T2.t2_id = T2.id What would you want it to do if T1_T2.t2_id has a value that isn't in T2? And should it do it for both T2 and T1? If using a NULL name is okay for both, you can look at outer joins, something like: select T1_T2.id, T1.name, T2.name from T1_T2 left outer join T1 on (T1_T2.t1_id = T1.id) left outer join T2 on (T1_T2.t2_id = T2.id) T1_T2 left outer join T1 on (T1_T2.t1_id = T1.id) will for example give you a row even if there's not a row in T1 with T1.id being the same as T1_T2.t1_id. In that case, you'll get the fields from T1_T2 and NULLs for the fields from T1. The same between that table and T2 occurs with the second outer join. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Query question
Thanks Stephan, My real DDL include a forign key reference to T2.id and since I am ok with NULL value then the "left outer join" indeed have solved the problem. Thanks again Medi On Thu, May 22, 2008 at 2:50 PM, Stephan Szabo <[EMAIL PROTECTED]> wrote: > On Thu, 22 May 2008, Medi Montaseri wrote: > > > Hi, > > I can use some help with the following query please. > > > > Given a couple of tables I want to do a JOIN like operation. Except that > one > > of the columns might be null. > > > > create table T1 ( id serial, name varchar(20) ); > > create table T2 ( id serial, name varchar(20) ); > > create table T1_T2 ( id serial, t1_id integer not null , t2_id integer ); > > > > Now I'd like to show a list of records from T1_T2 but reference T1 and T2 > > for the names instead of IDs. But T1_T2.t2_id might be null > > > > select T1_T2.id, T1.name , T2.name from T1, T2, T1_T2 > > where T1_T2.t1_id = T1.id and T1_T2.t2_id = T2.id > > What would you want it to do if T1_T2.t2_id has a value that isn't in T2? > And should it do it for both T2 and T1? If using a NULL name is okay for > both, you can look at outer joins, something like: > > select T1_T2.id, T1.name, T2.name from > T1_T2 left outer join T1 on (T1_T2.t1_id = T1.id) > left outer join T2 on (T1_T2.t2_id = T2.id) > > T1_T2 left outer join T1 on (T1_T2.t1_id = T1.id) will for example give > you a row even if there's not a row in T1 with T1.id being the same as > T1_T2.t1_id. In that case, you'll get the fields from T1_T2 and NULLs for > the fields from T1. The same between that table and T2 occurs with the > second outer join. > >
Re: [SQL] 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: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 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 numanumbsub --- 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 -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Extremely Low performance with ODBC
Sebastian Rychter wrote: Hi, I'm executing a query through psql ODBC which is taking around 2 minutes to complete. When I run it from PgAdmin it takes less than 3 seconds. The query itself has : . 15 inner joins (from just around 10 different tables -- the other inner joins are using different aliases for the same tables) . Select statement returns 1 field. . the testing database is selecting only 1 record. Taking a look at the explain analyze report, I see they are both quite the same and tested the ODBC driver through Visual Foxpro and Vb.NET as well, taking both around 2 minutes to finish. Any idea ? Don't know if this is your issue but we had a legacy VB/ODBC app that selected a handful (10 or fewer) records from a single table but for some reason behind the scenes we discovered that the app selected the whole table. As you might imagine, this caused very poor performance on large tables. EXPLAIN will not reveal this. You might want to set the server to log all transactions and see what the app is really doing at the server level. Cheers, Steve
