Re: Query Help

Joe,

Have you considered showing the number of used and new
items in 2 fields (new and used) for item.pack_num:

sum(case when item.pack_num = 1 then 1 else 0 end) as
new,
sum(case when item.pack_num = 2 then 1 else 0 end) as
used

If you try this approach, you'll need aggregate or use
GROUP BY for all other fields.

Best of luck,

Andrew Gould

--- Joe Koenig <joe(at)jwebmedia(dot)com> wrote:
> 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
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to
majordomo(at)postgresql(dot)org

__________________________________________________
Do You Yahoo!?
Check out Yahoo! Shopping and Yahoo! Auctions for all of
your unique holiday gifts! Buy at http://shopping.yahoo.com
or bid at http://auctions.yahoo.com