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