word id search query

melody

I'm having trouble with a query. Hope someone can help me out here.

Basically, I am making an adverts site. For the search, I've decided to use a word id table. Words in the title of an advert will be given a weight of 8, words from the text will be given a weight of 1. Here's the tables to make it a bit clearer:

ADVERTS:
id | title | text

WORDS:
word_id | word

ADVERTWORDS:
word_id | advert_id | weight

basically, I want to first select all the word ids from the words table where word='word1' and word='word2' etc. Then I want to be able to use these word_ids to search through advertwords for the relevant advert_id, SUM(weight) of each advert and use the advert ids to finally select the title and text fields from the adverts table. I want the whole thing to be ORDER BY SUM(advertwords.weight) DESC. I've tried using subqueries etc., I can't get any success. Please someone help me!

Btw, I'm using Postgres 6.4 (I have no choice in the matter...)

Thanks
Melody

melody

I've just got it to work! 😃 Here's the code for anyone that's interested. (Some of the table names may be slightly altered)

SELECT ads.title, ads.text, SUM(adwords.weight) WHERE words.word_id = adwords.word_id AND adwords.advert_id = ads.id AND words.word_id IN (SELECT DISTINCT word_id FROM words WHERE word='word1' OR word='word2') GROUP BY ads.id, ads.title, ads.text ORDER BY SUM(adwords.weight) DESC;

That's it!