X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 34AC8D1D730 for ; Wed, 12 May 2004 07:03:15 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 98755-10 for ; Wed, 12 May 2004 07:02:57 -0300 (ADT) Received: from www.postgresql.com (www.postgresql.com [200.46.204.209]) by svr1.postgresql.org (Postfix) with ESMTP id DF761D1E0E8 for ; Wed, 12 May 2004 07:02:53 -0300 (ADT) Received: from stan.ringways.co.uk (hub.ringways.co.uk [213.38.87.130]) by www.postgresql.com (Postfix) with ESMTP id 0074CCF54AB for ; Wed, 12 May 2004 07:02:51 -0300 (ADT) Received: from gary.ringways.co.uk ([10.1.1.2]) by stan.ringways.co.uk with esmtp (Exim 3.22 #1) id 1BNqZ0-0001LD-00 for pgsql-sql@postgresql.org; Wed, 12 May 2004 11:02:10 +0100 Content-Type: text/plain; charset="us-ascii" From: Gary Stainburn Organization: Ringways Garages Ltd To: Subject: view problem - too many rows out Date: Wed, 12 May 2004 11:02:31 +0100 User-Agent: KMail/1.4.3 MIME-Version: 1.0 Content-Transfer-Encoding: 8bit Message-Id: <200405121102.31554.gary.stainburn@ringways.co.uk> X-Virus-Scanned: by amavisd-new at postgresql.org X-Spam-Status: No, hits=1.3 tagged_above=0.0 required=5.0 tests=TONER X-Spam-Level: * X-Archive-Number: 200405/102 X-Sequence-Number: 17816 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