Hi, I rephrased my previous question in the hope of getting some feedback from you SQL experts. :-)

A simple query against my database outputs the following: mysql> select type,items.itemID from words,types,items,words_items -> where words.word like 'a%' -> and words.wordID=words_items.wordID -> and types.typeID=items.typeID -> and words_items.itemID=items.itemID -> order by type; +----------------+--------+ | type | itemID | +----------------+--------+ | Joke | 3 | | Joke | 3 | | Joke | 3 | | Joke | 3 | | Joke | 3 | | Novel | 1 | | Novel | 1 | | Novel | 1 | | Novel | 1 | | Novel | 2 | | Novel | 1 | | Novel | 1 | | Novel | 2 | | Novel | 1 | | Short Messages | 4 | | Short Messages | 4 | | Short Messages | 4 | +----------------+--------+

PROBLEM: How do I query my database so that I can find out the number of DISTINCT "items" in each "type" containing the word beginning with "a"?

I've tried many different combination of query statements similar to the following but still can't get what I expected. (the "no" is always incorrect)

mysql>select type, count(???) as no from ... . .. . . where words.word like 'a%' and .... ... . . . group by type;

The desired query *should* output the following result: +----------------+----+ | type | no | +----------------+----+ | Joke | 1 |(Only ONE distinct item, i.e. itemID=3) | Novel | 2 |(TWO distinct items, i.e. itemID=1 and itemID=2) | Short Messages | 1 |(Only ONE distinct item, i.e. itemID=4) +----------------+----+

Any help is highly appreciated.

Thanks and regards, Koo

###################################################################### CREATE TABLE items ( itemID int(8) DEFAULT '0' NOT NULL, typeID char(2) DEFAULT '0' NOT NULL, message text, PRIMARY KEY (itemID) );

INSERT INTO items VALUES (1,'1','this is almost about test alike'); INSERT INTO items VALUES (2,'1','hello baby'); INSERT INTO items VALUES (3,'2','two people alike'); INSERT INTO items VALUES (4,'3','alike baby hahaha');

CREATE TABLE types ( typeID int(11) DEFAULT '0' NOT NULL, type varchar(35), PRIMARY KEY (typeID) );

INSERT INTO types VALUES (1,'Novel'); INSERT INTO types VALUES (2,'Joke'); INSERT INTO types VALUES (3,'Short Messages'); INSERT INTO types VALUES (4,'Guestbook');

CREATE TABLE words ( wordID int(10) unsigned DEFAULT '0' NOT NULL auto_increment, word varchar(100) DEFAULT '' NOT NULL, PRIMARY KEY (wordID), UNIQUE word (word) );

INSERT INTO words VALUES (1,'about'); INSERT INTO words VALUES (2,'alike'); INSERT INTO words VALUES (3,'baby'); INSERT INTO words VALUES (4,'almost'); INSERT INTO words VALUES (5,'asad'); INSERT INTO words VALUES (6,'asadf'); INSERT INTO words VALUES (7,'asdffsa'); INSERT INTO words VALUES (8,'a908as'); INSERT INTO words VALUES (9,'ajkl8a'); INSERT INTO words VALUES (10,'a7jlasf');

CREATE TABLE words_items ( wordID int(10) unsigned DEFAULT '0' NOT NULL, itemID int(10) unsigned DEFAULT '0' NOT NULL, PRIMARY KEY (wordID,itemID) );

INSERT INTO words_items VALUES (1,1); INSERT INTO words_items VALUES (2,1); INSERT INTO words_items VALUES (2,3); INSERT INTO words_items VALUES (2,4); INSERT INTO words_items VALUES (3,1); INSERT INTO words_items VALUES (3,2); INSERT INTO words_items VALUES (3,4); INSERT INTO words_items VALUES (4,1); INSERT INTO words_items VALUES (5,1); INSERT INTO words_items VALUES (5,2); INSERT INTO words_items VALUES (5,3); INSERT INTO words_items VALUES (6,1); INSERT INTO words_items VALUES (7,1); INSERT INTO words_items VALUES (7,2); INSERT INTO words_items VALUES (7,3); INSERT INTO words_items VALUES (7,4); INSERT INTO words_items VALUES (8,3); INSERT INTO words_items VALUES (8,4); INSERT INTO words_items VALUES (9,3); INSERT INTO words_items VALUES (10,1);

--

--------------------------------------------------------------------- Please check "http://www.mysql.com/php/manual.php" before posting. To request this thread, e-mail mysq...@lists.mysql.com

To unsubscribe, send a message to: <mysql-unsubscribe-##L=##H...@lists.mysql.com>

If you have a broken mail client that cannot send a message to the above
address(Microsoft Outlook), you can use
http://lists.mysql.com/php/unsubscribe.php