How to query about all entries which occur less than n times

cy163 · #1 (**permalink**) 06-01-09, 00:38

Hello ALL,

I have a table as follows

Code:

ID   Cate.  		Word
1    Sports 		basketball
2    Sports  		football
3    SPorts  		volleyball

4    Economy		share
5    Economy		bank
6    Economy		credit
7    Economy		basketball

8    Society   		credit
9    Society		democracy
10   Society 		people
11   Society 		basketball

I would like to query about those words which occur in less than 3 (<3) categories.
In the above example, the query result should be all entries except for entries corresponding to 'basketball', i.e., all excluding entries 1, 7, 11.

I used the following statement

Code:

Select *
 From TableName
Group By
      Word
Having Count(*)<3
Order by
    Cate

However, I got less entries than expected by this statement.
For a word, only one entry is returned. e.g. for 'credit'

I have

Code:

6    Economy		credit

while I expect

Code:

6    Economy		credit
8    Society   		credit

it seems that the clause 'group by' lead to this result. How can I do to get the right answer?
Please help

r937 · #2 (**permalink**) 06-01-09, 03:16

Code:

SELECT *
  FROM TableName
 WHERE word IN
       ( SELECT word
           FROM TableName
         GROUP 
             BY Word
         HAVING COUNT(*) < 3 )
ORDER 
    BY Cate

gvee · #3 (**permalink**) 06-01-09, 19:31

My preference is to join instead of using the IN operator as a join will scale better

Code:

SELECT a.*
FROM   table_name As a
 INNER
  JOIN (
        SELECT word
        FROM   table_name
        GROUP
            BY word
        HAVING Count(*) < 3
       ) As b
    ON a.word = b.word
ORDER
    BY a.cate

r937 · #4 (**permalink**) 06-01-09, 20:11

good one george

pssst, fix your syntax error

gvee · #5 (**permalink**) 06-02-09, 08:11

Fixed-d-d-d

Internet Services

Web Development

Digital Marketing

Consumer Tech