Query Help

Try this:

1) Remove DISTINCT ON(item.description)
2) Add "GROUP BY item.description"
3) Add (after SELECT) "count(pack_num),"

--- Joe Koenig <joe@jwebmedia.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

<SNIP>

=====
Chris Albertson
Home: 310-376-1029 chrisalbertson90278@yahoo.com
Cell: 310-990-7550
Office: 310-336-5189 Christopher.J.Albertson@aero.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

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@jwebmedia.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@postgresql.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

Thanks for the responses - I was able to patch something together from
the responses. On to the next thing - I want to do a search based on a
reg ex. Does PostgreSQL have something like MySQL's "RLIKE"? I want to
pull all records in a category that start with a number 0-9. The best
way I saw was to do a UNION with all the numbers, but I figured there
was a better way. Thanks,

Joe

Chris Albertson wrote:

Postgres supports regular expressions

Check out section 4.5.2 POSIX reglar expressions

Darren

Darren Ferguson
Software Engineer
Openband

On Mon, 17 Dec 2001, Joe Koenig wrote:

For your very particular purpose, you may try to use

----------------
select * from table where substring(column, 0,1) between '0' and '9';
----------------

For a more general purpose, try the ~ operator.

\do will give you a list of available operators
You should maybe care about ~* operator also (case insensitive).

If you need to match one among many regular expressions, you can try to
store them in a little table, and then try:

----------------
select distinct table.* from table, regex_table where table.column ~
regex_table.regex;
----------------

DISTINCT is there to avoid that entries matching multiple regexps appear
multiple times.

No guarantees on performance.

HTH,

Antonio

Joe Koenig wrote: