Re: Select statement with except clause

Firstly, I want to thank you for responding.
Secondly, I wonder if I should only reply to the mailing list (I clicked
Reply All); if this is the case, I apologize for any inconvenience. Please
let me know so I reply correctly next time.

I will describe my issue with more detail. I need to perform 2 very similar
queries as follows:

*** QUERY 1 ***
SELECT fldA, fldB, fldC, SUM(fldD) AS fldD
FROM tableA
WHERE condition1
GROUP BY fldA, fldB, fldC

*** QUERY 2 ***
SELECT fldA, fldB, fldC, SUM(fldD)*(-1) AS fldD
FROM tableA
WHERE condition2
GROUP BY fldA, fldB, fldC

As you can see, both reference the same table and the same fields.

The differences between the queries are:
a) The last SELECTED field is multiplied by (-1) in the second query.
b) The WHERE conditions.

What I finally need is to exclude records generated by QUERY1 from QUERY2
when fldA, fldB and fldC are equal in both results.

With respect,
Jorge Maldonado

On Thu, May 23, 2013 at 1:36 PM, David Johnston <polobo(at)yahoo(dot)com> wrote:

> JORGE MALDONADO wrote
> > How does the EXCEPT work? Do fields should be identical?
> > I need the difference to be on the first 3 fields.
>
> Except operates over the entire tuple so yes all fields are evaluated and,
> if they all match, the row from the "left/upper" query is excluded.
>
> If you need something different you can use some variation of:
> IN
> EXISTS
> NOT IN
> NOT EXISTS
>
> with a sub-query (correlated or uncorrelated as your need dictates).
>
> For example:
>
> SELECT col1, col2, col3, sum(col4)
> FROM tbl
> WHERE (col1, col2, col3) NOT IN (SELECT col1, col2, col3 FROM tbl2) -- not
> correlated
> GROUP BY col1, col2, col3
>
> SELECT col1, col2, col3, sum(col4)
> FROM tbl
> WHERE NOT EXISTS (
> SELECT 1 FROM tbl AS tbl2 WHERE --make sure to alias the sub-query table
> if it matches the outer reference
> (tbl.col1, tbl.col2, tbl.col3) = (tbl2.col1, tbl2.col2, tbl2.col3)
> ) -- correlated; reference "tbl" within the query inside the where clause
> GROUP BY col1, col2, col3
>
> I do not follow your example enough to provide a more explicit
> example/solution but this should at least help point you in the right
> direction.
>
> David J.
>
>
>
>
>
>
>
>
>
> --
> View this message in context:
> http://postgresql.1045698.n5.nabble.com/Select-statement-with-except-clause-tp5756658p5756661.html
> Sent from the PostgreSQL - sql mailing list archive at Nabble.com.
>
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>