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