[mysql] SELECT COUNT(*) question

[Floris van den Berg]

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

[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.

[gortonc]

"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

[Peter Strömberg]

"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

[Floris van den Berg]

"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

[hexkid]

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

[Floris van den Berg]

"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