Skip site navigation (1) Skip section navigation (2)

view problem - too many rows out

From: Gary Stainburn <gary(dot)stainburn(at)ringways(dot)co(dot)uk>
To: <pgsql-sql(at)postgresql(dot)org>
Subject: view problem - too many rows out
Date: 2004-05-12 10:02:31
Message-ID: 200405121102.31554.gary.stainburn@ringways.co.uk (view raw or flat)
Thread:
Lists: pgsql-sql
Hi folks.

I've got a database for my consumable stock.  I have a consumables table 
containing the type details, e.g. HP 4100 toner. 

I then have a cons_stock table holding item details, one record per item (2 
toners = 2 rows).

I have a order_dets view which pulls in all the data required including order 
details (order number, state, supplier etc.), location description, supplier 
details etc.

This works fine.  I now want a variation of this, but instead of showing 
individual items I want to show only consumable types and a quantity.

Below are the two views, along with example output. Can anyone see where I've 
gone wrong?

create view order_dets as
  select
      c.cs_id, c.cs_make, c.cs_code, c.cs_type, cst.cst_desc, c.cs_colour,
      cs.cost_id, cs.cost_cl_id, cs.cost_supp, o.or_id,
      o.or_supp, o.or_date, o.or_received, o.or_no, orst.orst_desc as 
order_state,
      co.co_id, co.co_name, co.co_person, co.co_tel, 
      co.co_mobile, co.co_fax, co.co_email, co.co_type, 
      cl.cl_desc, c.cs_comments, cs.cost_comments
    from consumables c, orders o, cons_stock cs, contacts co, 
         cons_locations cl, cons_types cst, order_states orst
    where cs.cost_cs_id = c.cs_id 
      and cs.cost_or_id = o.or_id 
      and c.cs_type = cst.cst_id
      and o.or_supp = co.co_id
      and o.or_state = orst.orst_id
      and cs.cost_cl_id = cl.cl_id;

create view order_summary as
  select
      c.cs_id, cs.count as qty, c.cs_make, c.cs_code, c.cs_type, cst.cst_desc, 
c.cs_colour,
      o.or_id,
      o.or_supp, o.or_date, o.or_received, o.or_no, orst.orst_desc as 
order_state,
      co.co_id, co.co_name, co.co_person, co.co_tel, 
      co.co_mobile, co.co_fax, co.co_email, co.co_type, 
      c.cs_comments
    from consumables c, orders o, 
        (select cost_cs_id, cost_or_id, count(cost_cs_id) from cons_stock 
           where cost_or_id is not null
           group by cost_cs_id, cost_or_id
        ) cs, contacts co, 
         cons_locations cl, cons_types cst, order_states orst
    where cs.cost_cs_id = c.cs_id 
      and cs.cost_or_id = o.or_id 
      and c.cs_type = cst.cst_id
      and o.or_supp = co.co_id
      and o.or_state = orst.orst_id;

hardware=# select cs_id, cs_make, cs_code, cst_desc, cs_colour, cost_id, or_id 
from order_dets
hardware-# order by or_id, cs_id, cost_id;
 cs_id | cs_make | cs_code |    cst_desc     | cs_colour | cost_id | or_id
-------+---------+---------+-----------------+-----------+---------+-------
    31 | Kyocera | TK-50H  | Toner cartridge | BLACK     |      72 |     1
    31 | Kyocera | TK-50H  | Toner cartridge | BLACK     |      73 |     1
    31 | Kyocera | TK-50H  | Toner cartridge | BLACK     |      74 |     1
    32 | HP      | C3903A  | Toner cartridge | BLACK     |      79 |     1
    32 | HP      | C3903A  | Toner cartridge | BLACK     |      80 |     1
    17 | Epson   | T0442   | Ink cartridge   | CYAN      |      82 |     2
    24 | Epson   | S050097 | Toner cartridge | YELLOW    |      85 |     2
    29 | Brother | TN-6300 | Toner cartridge | BLACK     |      86 |     2
    32 | HP      | C3903A  | Toner cartridge | BLACK     |      87 |     2
    33 | PHILIPS | PFA331  | Ink Film        | BLACK     |      83 |     2
    33 | PHILIPS | PFA331  | Ink Film        | BLACK     |      84 |     2
     1 | HP      | C4096A  | Toner cartridge | BLACK     |      90 |     3
     1 | HP      | C4096A  | Toner cartridge | BLACK     |      91 |     3
     8 | HP      | C6578D  | Ink cartridge   | C-M-Y     |      88 |     3
     9 | HP      | C6615D  | Ink cartridge   | BLACK     |      89 |     3
     6 | HP      | C8061X  | Toner cartridge | BLACK     |      95 |     6
    16 | Epson   | T0441   | Ink cartridge   | BLACK     |      92 |     6
    18 | Epson   | T0443   | Ink cartridge   | MAGENTA   |      93 |     6
    19 | Epson   | T0444   | Ink cartridge   | YELLOW    |      94 |     6
    32 | HP      | C3903A  | Toner cartridge | BLACK     |      96 |     6
    32 | HP      | C3903A  | Toner cartridge | BLACK     |      97 |     6
    34 | SAMSUNG | SF-5100 | Ink Film        | BLACK     |      98 |     6
(22 rows)

hardware=# select cs_id, cs_make, cs_code, cst_desc, cs_colour, qty, or_id 
from order_summary
hardware-# order by or_id, cs_id;
 cs_id | cs_make | cs_code |    cst_desc     | cs_colour | qty | or_id
-------+---------+---------+-----------------+-----------+-----+-------
    31 | Kyocera | TK-50H  | Toner cartridge | BLACK     |   3 |     1
    31 | Kyocera | TK-50H  | Toner cartridge | BLACK     |   3 |     1
    31 | Kyocera | TK-50H  | Toner cartridge | BLACK     |   3 |     1
    31 | Kyocera | TK-50H  | Toner cartridge | BLACK     |   3 |     1
    31 | Kyocera | TK-50H  | Toner cartridge | BLACK     |   3 |     1
    31 | Kyocera | TK-50H  | Toner cartridge | BLACK     |   3 |     1
    31 | Kyocera | TK-50H  | Toner cartridge | BLACK     |   3 |     1
    32 | HP      | C3903A  | Toner cartridge | BLACK     |   2 |     1
    32 | HP      | C3903A  | Toner cartridge | BLACK     |   2 |     1
    32 | HP      | C3903A  | Toner cartridge | BLACK     |   2 |     1
    32 | HP      | C3903A  | Toner cartridge | BLACK     |   2 |     1
    32 | HP      | C3903A  | Toner cartridge | BLACK     |   2 |     1
    32 | HP      | C3903A  | Toner cartridge | BLACK     |   2 |     1
    32 | HP      | C3903A  | Toner cartridge | BLACK     |   2 |     1
    17 | Epson   | T0442   | Ink cartridge   | CYAN      |   1 |     2
    17 | Epson   | T0442   | Ink cartridge   | CYAN      |   1 |     2
    17 | Epson   | T0442   | Ink cartridge   | CYAN      |   1 |     2
    17 | Epson   | T0442   | Ink cartridge   | CYAN      |   1 |     2
[snip]
    32 | HP      | C3903A  | Toner cartridge | BLACK     |   2 |     6
    34 | SAMSUNG | SF-5100 | Ink Film        | BLACK     |   1 |     6
    34 | SAMSUNG | SF-5100 | Ink Film        | BLACK     |   1 |     6
    34 | SAMSUNG | SF-5100 | Ink Film        | BLACK     |   1 |     6
    34 | SAMSUNG | SF-5100 | Ink Film        | BLACK     |   1 |     6
    34 | SAMSUNG | SF-5100 | Ink Film        | BLACK     |   1 |     6
    34 | SAMSUNG | SF-5100 | Ink Film        | BLACK     |   1 |     6
    34 | SAMSUNG | SF-5100 | Ink Film        | BLACK     |   1 |     6
(112 rows)

hardware=#
-- 
Gary Stainburn
 
This email does not contain private or confidential material as it
may be snooped on by interested government parties for unknown
and undisclosed purposes - Regulation of Investigatory Powers Act, 2000     


Responses

pgsql-sql by date

Next:From: Christian KratzerDate: 2004-05-12 10:13:24
Subject: Re: [SQL] \set
Previous:From: Jie LiangDate: 2004-05-11 23:07:04
Subject: \set

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group