Message-ID: <467A8142.1050508@lake-lotawana.mo.us>
Date: Thu, 21 Jun 2007 08:46:42 -0500
From: "A. R. Van Hook" <hook@lake-lotawana.mo.us>
User-Agent: Thunderbird 1.5.0.12 (X11/20070530)
MIME-Version: 1.0
To: postgresql sql list <pgsql-sql@postgresql.org>
Subject: join problem
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

I have three tables relating to purchases
      invoice      - transaction data (customer id, deposit. etc)
      invoiceitems - purachace items detail
      cai          - customer data
if I query for the total charges using
   select sum(rowtot + tax)
    from invoiceitems
   where ivid in (select ivid from invoice where cusid = 2128)"

I get 1179.24 which is correct.

if I query for the total deposit using
   select sum(deposit)
   from invoice
   where cusid = 2128"

I also get 1179.24, also the correct amount


If I try an inclusive query using the following:
     select
      sum(i.rowtot + i.tax) as tot,
      sum(v.deposit) as deposit
    from cai c
    join invoice           v on (v.cusid = c.cusid)
    left join invoiceitems i on (v.ivid = i.ivid)
    where v.cusid = 2128
    group by 
      c.cusid
I get
    tot    | deposit
----------+---------
 1179.240 | 2819.24

Can someone correct the query?


thanks


tables definations are as follows:

 invoice 
        (ivid                int NOT NULL PRIMARY KEY,
         rid                 int null references registry,
         sid                 int not null  references staffname,
         cusid               int,
         invdate             date,
         ifname              varchar(16),
         imi                 char,
         ilname              varchar(16),
         addr                text,
         city                varchar(16),
         state               varchar(2),
         zip                 varchar(16),
         iphone              varchar(16),
         eventdate           date,
         paytype             int,
         bust                varchar(16),
         height              varchar(16),
         dressize            varchar(16),
         waist               varchar(16),
         hips                varchar(16),
         hollow              varchar(16),
         deposit             numeric(6,2),
         transtype           int,
         notes               text,
         neck                varchar(16),
         arm_length          varchar(16),
         leg_length          varchar(16),
         coat                varchar(16),
         shoe                varchar(16),
         tux                 int    default 0

invoiceItems 
        (item                int NOT NULL,
         ivid                int NOT NULL references invoice ON DELETE 
CASCADE,
         qty                 int,
         stid                int  references stock, /*tag*/
         descript            text,
         price               numeric(6,2),
         tax                 numeric(7,3),
         discount            numeric(6,2),
         rowtot              numeric(7,3),
         pickup              int default 0,   /* SO or to be picked up = 
1 */
         primary key    (item, ivid)

create table cai        /* customer account  information*/
       (cusid                int NOT null primary key,
        cfname               varchar(16),
        cmi                  char  default '',
        clname               varchar(16),
        caddr                text,
        ccity                varchar(16),
        cstate               varchar(2),
        czip                 varchar(16),
        cphone               varchar(16),
        db                   numeric(7,2),
        tcode                int not null default 0,
        acode                int not null default 0,
        tdate                timestamp not null
[hook@f6 ~]$


-- 
Arthur R. Van Hook
     Mayor - Retired
 The City of Lake Lotawana

hook@lake-lotawana.mo.us
hook@lota.us
avanhook3@comcast.net
(816) 578-4704 - Home