MYSQL query joining table problem 2

Question

I have this request -

SELECT interest_desc, categoryID, MAX(num_in_cat) AS num_in_cat FROM ( SELECT interest_desc, categoryID, COUNT(categoryID) AS num_in_cat FROM interests GROUP BY interest_desc, categoryID ) subsel GROUP BY interest_desc, categoryID 

I want to change it to eventually display the category name from a separate table named categories . All I can display is categoryID from interests with this sql

Both table structures

 #interests CREATE TABLE `interests` ( `interestID` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(100) NOT NULL, `categoryID` int(11) NOT NULL, `sessionID` int(11) NOT NULL, `interest_desc` varchar(30) NOT NULL, `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`interestID`) ) ENGINE=MyISAM AUTO_INCREMENT=9 DEFAULT CHARSET=utf8 

Category table structure

 # categories CREATE TABLE `categories` ( `categoryID` int(11) NOT NULL AUTO_INCREMENT, `category_desc` varchar(100) NOT NULL, PRIMARY KEY (`categoryID`) ) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 

I know some kind of connection is required, but I looked at the examples and struggled to get the exact syntax.

I have this in a php script - this is an echo statement - this is

 "{$result['interest_desc']} was the most popular in category {$result['categoryID']} with {$result['num_in_cat']} occurrences\n"; 

and its conclusion -

 "Adidas was the most popular in category 5 with 1 occurrences" 

I want the output to be "Adidas was the most popular in Sport with 1 entry"

However, my sql query does not contain category_desc .

Answer 1

This is faster performance.

 SELECT subsel.interest_desc, subsel.categoryID, cat.category_desc, MAX(num_in_cat) AS num_in_cat FROM ( SELECT interest_desc, categoryID, COUNT(categoryID) AS num_in_cat FROM interests GROUP BY interest_desc, categoryID ) subsel inner join categories as cat on subsel.categoryID = cat.categoryID GROUP BY interest_desc, subsel.categoryID 

Answer 2

Please check this, it will give you the desired result.

 SELECT subsel.interest_desc, cat.category_desc, MAX(num_in_cat) AS num_in_cat FROM ( SELECT interest_desc, categoryID, COUNT(categoryID) AS num_in_cat FROM interests GROUP BY interest_desc, categoryID ) subsel inner join categories as cat on subsel.categoryID = cat.categoryID GROUP BY interest_desc, subsel.categoryID 

Answer 3

SELECT * FROM interests i LEFT JOIN categories c ON i.categoryID = c.categoryID

I have not tested it. There may be syntax errors.

Answer 4

I do not know in which realistic scenarios two similar questions that you posted make sense. I would say that you can go with this right away:

 SELECT i.interest_desc, c.category_desc, COUNT(i.categoryID) AS num_in_cat FROM interests AS i INNER JOIN categories AS c USING (categoryID) GROUP BY i.interest_desc, i.categoryID