Query Help

From: Joe Koenig <joe(at)jwebmedia(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Query Help
Date: 2001-12-17 16:21:38
Message-ID: 3C1E1B92.F3EC6082@jwebmedia.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I have an existing query to pull some product info from my db - there is
a field, item.pack_num, that tells whether the item is new (1) or used
(2). When I display the listing to the web users, I want to be able to
tell them if a used item is available for each item in the list.
However, I want this to all be done with 1 query - so what I'm wondering
is, is there a way to modify my exising query (below) to have it give
the the item with the pack_num of 2, if there are new and used items in
the db. The DISTINCT ON(item.description) is there because if there is a
new and used item, the item is listed in the db twice, once for each
pack_num (I know, bad layout - not my idea...). I don't want the query
to only return used items. The ideal thing would be for it to return all
pack_num's available for that item, but only 1 title (description). I
think that is asking a bit much though. Thanks.

SELECT DISTINCT ON(item.description) item.description AS description,
item.item_num AS item_num, item.comments AS comments, item.pack_num AS
pack_num, dept.description AS category, price.price AS price FROM item,
dept, price WHERE item.dept_num = '91' AND item.sub_dept_num = '200' AND
item.dept_num = dept.dept_num AND item.item_num = price.item_num AND
item.pack_num = price.pack_num;

Also, if something is horribly wrong with my query don't hesitate to
tell me. Thanks Again,

Joe

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Chris Albertson 2001-12-17 16:29:19 Re: Query Help
Previous Message Herb Blacker 2001-12-17 15:42:03 Templates