Show Bid Request
SQL Query design
Bid Request Id: 37145
|
|
|
|
Posted by: |
kdundon (2 ratings)
(Software buyer rating 10 )
|
|
Non-action Ratio: |
Very Good - 0.00%
|
|
Posted: |
Nov 26, 2002
8:51:49 AM EDT
|
|
Bidding Closes: |
Nov 29, 2002
8:55:37 AM EDT
|
|
Viewed (by coders): |
353 times
|
|
Deadline: |
12/6/2002
TIME EXPIRED
|
|
|
|
|
Description:
I need a sql query that will optimise searching a 2 column table; img_id int, word_num int.
Img_id contains a number associated with an image, word_num contains values associated with a keyword which is belonging to that image.
The table surrently has over 2 million rows, note an image may have up to 500 word_nums associated with it.
I need a query that will return results for the following questions for example:
Give me all img_id&'s that have word num 44809 and 44756 or 45829 not 50076
I have some queries that I have been working on :
select distinct i.img_id, img_sku, img_cdnr,i.sorting as img_cdnr from
image i inner join (select img_id from image_key (index =idx_word_num) where
(word_num in (44809)) or
(word_num = 44756 or word_num = 45829)
group by img_id having count(*) > 1) k on i.img_id = k.img_id
and
k.img_id not in (select img_id from image_key (index =idx_word_num) where word_num in (50076))
order by i.sorting desc
select distinct img_id from image_key where img_id in (select img_id from image_key (index =idx_word_num) where word_num
in (44809,44756) group by img_id having count(*) > 1
union
select img_id from image_key (index =idx_word_num) where word_num
in (44809,45829) group by img_id having count(*) > 1)
and img_id not in (select img_id from image_key (index =idx_word_num) where word_num in (50076))
The query will have to allow unlimited “and” “or” and “not” so it could be select * where img_id is (1 and 2 and 3 and 4 and 5 and 6) or (7 or 8 or 9 or 10) but not (87,65,43,21)
I&'m looking for something that will return fast results in PostgreSQL, however ANSI SQL 92 syntax will work on postgre the only problem so I have found is that I can&'t give it an optimiser hint like I do in SQL.
I will also accept an answer that suggests another and better method of achieving the result I need, even if it means redesign of the tables.
Deliverables:
1) Complete and fully-functional working query
Platform:
POSTGRESQL or ANSI92 SQL
Must be 100% finished and received by buyer on:
Dec 6, 2002 EDT
Deadline legal notes: All times are expressed in the time zone of the site EDT (UT - 5). If the buyer omitted a time, then the deadline is 11:59:59 PM EDT on the indicated date.
Remember that contacting the other party outside of the site (by email, phone, etc.) on all business projects < $500 (before the buyer's money is escrowed) is a violation of both the software buyer and seller agreements.
We monitor all site activity for such violations and can instantly expel transgressers on the spot, so we thank you in advance for your cooperation.
If you notice a violation please help out the site and report it. Thanks for your help.
|
|
|
Bidding/Comments:
|
All monetary amounts on the site are in United States dollars.
Rent a Coder is a closed auction, so coders can only see their own bids and comments. Buyers can view every posting made on their bid requests. |
See all rejected bids (and all comments)
| Name |
Bid Amount
|
Date |
Coder Rating |
|
|
|
|
N/A
|
Nov 26, 2002
11:43:32 AM EDT
|
10
(Excellent)
|
|
|
Dear kdundon,
Will it be possible for you to send me the table structure and some sample data so that I can try it out locally at my end to suggest you the bestquery / alternative?.
Regards,
Jinesh
|
|
|
|
|
|
|
