pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Marshall Spight <marshall@meetstheeye.com>
To: pgsql-sql@postgresql.org
Subject: Re: UNION and rows improperly unified: query optimization question
Date: Fri, 15 Feb 2002 23:25:24 -0800
Message-ID: <a4l1h4$vkr$1@jupiter.hub.org> (raw)
References: <20020211200524.GA17170@wotan>

"Henry House" <hajhouse@houseag.com> wrote in message news:20020211200524.GA17170@wotan...
> Greetings. I have an accounting system in Postgres which has tables
> 'transact' and 'gl_entry'. Each check, deposit, etc has one entry in
> transact, but there may be multiple entries in gl_entry if one check was for
> multiple expenses that are tracked separately (for example, one check to AT=
> &T
> might cover both telephone and Internet service, which are two different
> costing categories). Also, debit and credit are supposed to appear in
> seperate columns, even though they are one number with either positive or
> negative sign in the table. So I run two select and combine them
> using a union statement, but this improperly combines two gl_entry lines th=
> at
> do not differ in amount or transaction ID. My solution is to select also the
> unique ide number from gl_entry and remove it by wrapping the
> SELECT...UNION...SELECT in another SELECT. This seems awfully ugly. Is there
> a better way?

The result of a SELECT statement is a set of values, in the mathematical sense of
the word "set". Sets do not have duplicate values, so here the database is behaving
correctly. That is what UNION means, in SQL, and in any math textbook.

What's wrong with just the inner select? Why do you have to get rid of the
unique id? It's not hurting anything; it's helping in fact.


Marshall






view thread (4+ messages)

Message-ID: <a4l1h4$vkr$1@jupiter.hub.org>
Permalink:  ../a4l1h4$vkr$1@jupiter.hub.org/
Also on:    postgresql.org/message-id/a4l1h4$vkr$1@jupiter.hub.org

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: marshall@meetstheeye.com
  Subject: Re: UNION and rows improperly unified: query optimization question
  In-Reply-To: <a4l1h4$vkr$1@jupiter.hub.org>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox