471,887 Members | 1,043 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

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

[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 12259
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
reply views Thread by zermasroor | 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.