How can I do this without subqueries?

[Jay Donnell]

I have a table with this structure
CREATE TABLE `occurrence` (
`occurrence_id` int(10) unsigned NOT NULL auto_increment,
`word_id` int(10) unsigned NOT NULL default '0',
`productId` int(10) unsigned NOT NULL default '0',
PRIMARY KEY (`occurrence_id`)
);

The following query will show me how many times 'something' occurred
for each productId

SELECT productId, count(*) as occurrences
FROM occurrence AS o, word AS w
WHERE o.word_id = w.word_id and word_word = 'something' group by
productId order by occurrences

I want to be able to find out how many times multiple words occur for
each productId.
I want to be able to do something like this.

SELECT productId, count(*) as occurrences
FROM occurrence AS o, word AS w
WHERE o.word_id = w.word_id and word_word = 'something1' order by
occurrences
union
SELECT productId, count(*) as occurrences
FROM occurrence AS o, word AS w
WHERE o.word_id = w.word_id and word_word = 'something2' order by
occurrences

but have it sum the occurrences of 'something1' and 'something2' for
each productId.

I hope that makes sense. If not just ask and I'll clarify it.

[Bill Karwin]

Jay Donnell wrote:

I want to be able to find out how many times multiple words occur for
each productId.

Does this do what you want?

SELECT productId, count(*) AS occurrences
FROM occurrence AS o INNER JOIN word AS w ON o.word_id = w.word_id
WHERE word_word IN ('something1', 'something2')
GROUP BY productId
ORDER BY occurrences

If you want it to subtotal by productId and by word, try this:

SELECT productId, word_word count(*) AS occurrences
FROM occurrence AS o INNER JOIN word AS w ON o.word_id = w.word_id
WHERE word_word IN ('something1', 'something2')
GROUP BY productId, word_word
ORDER BY occurrences

Regards,
Bill K.

[Jay Donnell]

Neither of those do what I'm looking for. Let me try to explain it
better.

There is a product table that contains the descriptions of the
products. Let's say that a description is this.
"SS Cubic Zirconia Bracelet 7;
Tennis bracelet in a league of its own. You won't find anything quite
like this stunning cubic zirconia tennis bracelet. Its figure styling,
lovely 18k gold (40 mils) over sterling silver setting and radiant
beauty sets it apart.
"

The word table contains every word that appears in all the
descriptions and the occurrence table contains an entry for every
instance of a word in all the descriptions. I.e. there is a unique
entry in the word table for each word and a seperate entry in the
occurrence table for every occurrence of that a word.

I want a query that will tell me the number of times a set of keywords
appears for each product. If the keywords are 'bracelet' and 'cubic'
'zirconia' then this description will have a count of 7. Bracelet 3
times, cubic 2, and zirconia 2.

I'm currently using this query which works for a single keyword.

SELECT productId, count(*) as occurrences
FROM occurrence AS o, word AS w
WHERE o.word_id = w.word_id and word_word = 'keyword' group by
productId order by occurrences desc limit 0, 650

I run it once for each keyword and sum them in the php code. I was
hoping that there would be a way to do this with a single query which
should be a lot faster.

I hope that helps explain it better.

Does this do what you want?

SELECT productId, count(*) AS occurrences
FROM occurrence AS o INNER JOIN word AS w ON o.word_id = w.word_id
WHERE word_word IN ('something1', 'something2')
GROUP BY productId
ORDER BY occurrences

If you want it to subtotal by productId and by word, try this:

SELECT productId, word_word count(*) AS occurrences
FROM occurrence AS o INNER JOIN word AS w ON o.word_id = w.word_id
WHERE word_word IN ('something1', 'something2')
GROUP BY productId, word_word
ORDER BY occurrences

[Bill Karwin]

Jay Donnell wrote:

I want a query that will tell me the number of times a set of keywords
appears for each product. If the keywords are 'bracelet' and 'cubic'
'zirconia' then this description will have a count of 7. Bracelet 3
times, cubic 2, and zirconia 2.

Yes, I have implemented similar designs. Given your description, I
still believe that this query gives you what you need:

SELECT o.productId, count(*) AS occurrences
FROM occurrence AS o INNER JOIN word AS w ON o.word_id = w.word_id
WHERE w.word_word IN ('bracelet', 'cubic', 'zirconia')
GROUP BY o.productId
ORDER BY occurrences DESC
LIMIT 650

I tried this out in my test database, given the sample data you gave,
and it does return a count of 7 as the value for occurrences.

Regards,
Bill K.