Thread: Query Help
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
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: > > 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 > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html
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: > 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: > > > > 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 > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 5: Have you checked our extensive FAQ? > > > > http://www.postgresql.org/users-lounge/docs/faq.html > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org >
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: >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 >