CODEWITHSUNDEEP
mysqljoingroup-by
DIM3NSION
DIM3NSION

Reputation: 1761

MYSQL query, joining 2 table's issue

I have this query - 

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 so that I can eventually display the category name from a separate table called categories. All I can display is the categoryID from interests with this sql

Both table structures are

#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

categories 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 a join of some sort is needed but I have looked at examples and are struggling to get the exact syntax.

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

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

and its output is this - 

"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 occurrences"

However my sql query does not feature category_desc.

Upvotes: 6

Views: 136

Answers (4)

Maulik Vora
Maulik Vora

Reputation: 2584

This is more quick performance wise

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

Upvotes: 2

nawfal
nawfal

Reputation: 73163

I do not know in what realistic scenarios the two similar queries as you posted make sense. I would say you can go with this straightaway:

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

Upvotes: 0

Shaikh Farooque
Shaikh Farooque

Reputation: 2630

Kindly check this , It will give you the required 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

Upvotes: 1

user854595
user854595

Reputation:

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

I haven't tested it. There might be syntax errors.

Upvotes: 0

Related Questions