view problem - too many rows out - Mailing list pgsql-sql

From Gary Stainburn
Subject view problem - too many rows out
Date
Msg-id 200405121102.31554.gary.stainburn@ringways.co.uk
Whole thread Raw
Responses solved Re: view problem - too many rows out
List 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_locationscl, 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  |
InkFilm        | 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  |
Tonercartridge | 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   |
Inkcartridge   | 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     



pgsql-sql by date:

Previous
From: "Jie Liang"
Date:
Subject: \set
Next
From: "William Anthony Lim"
Date:
Subject: Re: working with schema