RE: [PHP] select.. group... subselect.. whatever? Please Help..!

I'm not exactly sure how to solve your problem, but you can streamline this query:: mysql> select distinct items.itemID,words.word,types.type from types,items,words,words_items -> where words.wordID=words_items.wordID -> and words.word like 'a%' -> and types.typeID=items.typeID -> and items.itemID=words_items.itemID -> ; by changing it to: SELECT DISTINCT items.itemID,words.word,types.type FROM words LEFT JOIN words_items ON words.wordID=words_items.wordID LEFT JOIN items ON words_items.itemID=items.itemID LEFT JOIN types ON items.typeID=types.typeID WHERE words.word LIKE 'a%'; --Bruce Bruce Christensen Intel Corporation brucex.r.christensen@intel.com -----Original Message----- From: Koo [mailto:mysql@sun-scope.com] Sent: Saturday, August 05, 2000 11:59 AM To: php-general@lists.php.net Subject: [PHP] select.. group... subselect.. whatever? Please Help..! Hello, I have a problem about SQL query statement... This is my current SQL output: mysql> select distinct items.itemID,words.word,types.type from types,items,words,words_items -> where words.wordID=words_items.wordID -> and words.word like 'a%' -> and types.typeID=items.typeID -> and items.itemID=words_items.itemID -> ; +--------+---------+----------------+ | itemID | word | type | +--------+---------+----------------+ | 1 | about | Novel | | 1 | alike | Novel | | 1 | almost | Novel | | 1 | asad | Novel | | 2 | asad | Novel | | 1 | asadf | Novel | | 1 | asdffsa | Novel | | 2 | asdffsa | Novel | | 1 | a7jlasf | Novel | | 3 | alike | Joke | | 3 | asad | Joke | | 3 | asdffsa | Joke | | 3 | a908as | Joke | | 3 | ajkl8a | Joke | | 4 | alike | Short Messages | | 4 | asdffsa | Short Messages | | 4 | a908as | Short Messages | +--------+---------+----------------+ 17 rows in set (0.01 sec) PROBLEM: How do I query the database so that I can get the number of non-duplicated ITEMs in each TYPE that contains a word beginning with character "a". This is the result that I expected (row order not important): +----------------+----+ | type | no | +----------------+----+ | Joke | 1 |(Only ONE item, i.e. itemID=3) | Novel | 2 |(TWO items, i.e. itemID=1 and itemID=2) | Short Messages | 1 |(Only ONE item, i.e. itemID=4) +----------------+----+ I'VE DONE THE FOLLOWING, BUT IT DOESN'T OUTPUT THE RESULT I WANTED: mysql> SELECT distinct types.type,count(*) as no -> FROM types, words, words_items, items -> WHERE words.word like 'a%' -> and words.wordID=words_items.wordID -> AND types.typeID=items.typeID -> and items.itemID=words_items.itemID -> GROUP BY type; +----------------+----+ | type | no | +----------------+----+ | Joke | 5 |(5[itemID=3]) | Novel | 9 |(7[itemID=1]+2[itemID=2]) | Short Messages | 3 |(3[itemID=4]) +----------------+----+ It simply reports the total instances of items that match the criteria... Hope that further explains what I want... Sorry for the troubles. I've spent almost 2 days on thinking and trying different combinations of query statement but in vain. I'm not an SQL expert after all. Thank you very much. Regards, Koo -- PHP General Mailing List (http://www.php.net/) To unsubscribe, e-mail: php-general-unsubscribe@lists.php.net For additional commands, e-mail: php-general-help@lists.php.net To contact the list administrators, e-mail: php-list-admin@lists.php.net