471,083 Members | 1,151 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,083 software developers and data experts.

How can I do this without subqueries?

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.
Jul 20 '05 #1
3 1998
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.

Jul 20 '05 #2
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

Jul 20 '05 #3
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.
Jul 20 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

6 posts views Thread by pete | last post: by
6 posts views Thread by Daniel Elliott | last post: by
2 posts views Thread by orin | last post: by
debasisdas
reply views Thread by debasisdas | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.