469,336 Members | 5,360 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,336 developers. It's quick & easy.

[mysql] SELECT COUNT(*) question

Is something like the following possible?

SELECT products.id, count(*) as counter FROM products, keywords WHERE
keywords.keyword in ("bla","hi","hello") AND counter > 2;

Can't get it to work :/

Floris
Jul 17 '05 #1
6 12158
sam

"Floris van den Berg" <fl******@wxs.nl> wrote in message
news:bk**********@reader08.wxs.nl...
Is something like the following possible?
No.
SELECT products.id, count(*) as counter FROM products, keywords WHERE
keywords.keyword in ("bla","hi","hello") AND counter > 2;


If you explain what you want to do, may be we can help.
Jul 17 '05 #2

"Floris van den Berg" <fl******@wxs.nl> wrote in message
news:bk**********@reader08.wxs.nl...
Is something like the following possible?

SELECT products.id, count(*) as counter FROM products, keywords WHERE
keywords.keyword in ("bla","hi","hello") AND counter > 2;

Can't get it to work :/

Floris


You've not specified the table join and to use the count function you need
to use GROUP BY clause. Having said that I don't think the counter>2 will
work anyway, not allowed to use the results of group function for extraction
criteria. You'll probably need to create a temp table with the count results
and then extract the final result set from that. Sub queries may do the
trick as well but depends on what version of Mysql you have.

Rgds,
Chris
Jul 17 '05 #3
"Floris van den Berg" <fl******@wxs.nl> wrote in news:bkejov$kjt$1
@reader08.wxs.nl:
Is something like the following possible?

SELECT products.id, count(*) as counter FROM products, keywords WHERE
keywords.keyword in ("bla","hi","hello") AND counter > 2;

Can't get it to work :/


SELECT products.id, count(*) as counter
FROM products, keywords
WHERE keywords.keyword in ("bla","hi","hello")
GROUP BY products.id
HAVING counter > 2

--
Peter Strömberg
C2K2 C2K3 ISCCIV02
Jul 17 '05 #4
"sam" <rb*****@caramail.com> schreef in bericht
news:bk***********@news.cybercity.dk...

"Floris van den Berg" <fl******@wxs.nl> wrote in message
news:bk**********@reader08.wxs.nl...
Is something like the following possible?


No.
SELECT products.id, count(*) as counter FROM products, keywords WHERE
keywords.keyword in ("bla","hi","hello") AND counter > 2;


If you explain what you want to do, may be we can help.


Okay. What i want to do is create a search page on an e-commerce website. I
have a table with products (id, title, price) and a table with keywords (id,
keyword). I have an array of strings i want to lookup in the keywords table
(and i do have a GROUP BY as gortonc correctly remarked). The idea is that a
product is only found if *all* given keywords are found in a product title.
So i count the found entries and only accept those entries that have a count
larger than the size of the keywords array.

Floris
Jul 17 '05 #5
Floris van den Berg wrote...
Is something like the following possible?

SELECT products.id, count(*) as counter FROM products, keywords WHERE
keywords.keyword in ("bla","hi","hello") AND counter > 2;

Can't get it to work :/

Floris

try this:

select products.id, count(*) as counter
from products, keywords
where keywords.keyword in ("bla","hi","hello")
group by products.id
having count(*) > 2
Jul 17 '05 #6
"hexkid" <he****@hotpop.com> schreef in bericht
news:da**************************@posting.google.c om...
Floris van den Berg wrote...
Is something like the following possible?

SELECT products.id, count(*) as counter FROM products, keywords WHERE
keywords.keyword in ("bla","hi","hello") AND counter > 2;

Can't get it to work :/

Floris

try this:

select products.id, count(*) as counter
from products, keywords
where keywords.keyword in ("bla","hi","hello")
group by products.id
having count(*) > 2


Thanks! That works!

Floris
Jul 17 '05 #7

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

6 posts views Thread by knoak | last post: by
reply views Thread by Philip Stoev | last post: by
reply views Thread by Mike Chirico | last post: by
4 posts views Thread by Ross Contino | last post: by
7 posts views Thread by Schraalhans Keukenmeester | last post: by
14 posts views Thread by dottty | last post: by
6 posts views Thread by ojorus | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
1 post views Thread by Marylou17 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.