Mind blank!

[Adam]

Hi all. Been trying to work out this query for a while now, but the mind's gone blank and I'm getting stressed!

 

Basically trying to select categories from a table, and also a count of the products that exist in another table, within that category. If that makes sense? Very similar idea to forums showing the title of a post and the count of replies.

 

I've tried allsorts; sub-queries, joins, etc. I just can't think how I could do it.

 

Can anyone help?

 

Thanks

[xtopolis]

This is fairly inconvenient to guess at without table structures.

[Adam]

Pardon me, I was just mainly thinking along the lines of a tutorial. But..

 

Basically there's two tables, "cats" and "prods". Cats contains 'class_name' and 'mercedes' (amongst a few other things). Prods contains model_id, class_name, etc. So basically for each class returned from cats (that 'mercedes' = 0), I want to perform a count on prods to see how many products are in that class_name.

 

Any better?

 

Thanks

[xtopolis]

Hah... I went to post this and got caught up reading about MySQL optimization..

 

Possible Method One [using 2 tables, 1 category per product]:

SELECT c.catName, COUNT(p.pid)
FROM categories c
JOIN products p USING(cid)
/*WHERE c.cid = 2*/
GROUP BY c.cid

I included a commented line for specifying just one category.

 

Table 1 - categories

-- Table structure for table `categories`
-- 

CREATE TABLE `categories` (
  `cid` int(2) NOT NULL auto_increment,
  `catName` varchar(255) collate latin1_german2_ci NOT NULL,
  PRIMARY KEY  (`cid`)
) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=latin1 COLLATE=latin1_german2_ci AUTO_INCREMENT=4 ;

-- 
-- Dumping data for table `categories`
-- 

INSERT INTO `categories` VALUES (1, 'Toyota');
INSERT INTO `categories` VALUES (2, 'Ford');
INSERT INTO `categories` VALUES (3, 'Mercedes');

Table 2 - products

-- Table structure for table `products`
-- 

CREATE TABLE `products` (
  `pid` int(5) NOT NULL auto_increment,
  `prodName` varchar(255) collate latin1_german2_ci NOT NULL,
  `cid` int(11) NOT NULL,
  PRIMARY KEY  (`pid`)
) ENGINE=MyISAM AUTO_INCREMENT=7 DEFAULT CHARSET=latin1 COLLATE=latin1_german2_ci AUTO_INCREMENT=7 ;

-- 
-- Dumping data for table `products`
-- 

INSERT INTO `products` VALUES (1, 'Focus', 1);
INSERT INTO `products` VALUES (2, 'Scion', 2);
INSERT INTO `products` VALUES (3, 'M-Class', 3);
INSERT INTO `products` VALUES (4, 'CoolCar', 1);
INSERT INTO `products` VALUES (5, 'RedCar', 2);
INSERT INTO `products` VALUES (6, 'Blue Car', 1);

 

#######################################################

 

Possible Method Two [using 3 tables, multiple categories per product]:

SELECT c.catName,COUNT(p.prodName)
FROM categories c
JOIN cat_prod cp USING(cid)
JOIN products p USING(pid)
/* WHERE c.cid = 2 */
GROUP BY c.cid

I included the commented WHERE line so that you could specify one type if you wanted.

 

Table 1 - categories

-- Table structure for table `categories`
-- 

CREATE TABLE `categories` (
  `cid` int(2) NOT NULL auto_increment,
  `catName` varchar(255) collate latin1_german2_ci NOT NULL,
  PRIMARY KEY  (`cid`)
) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=latin1 COLLATE=latin1_german2_ci AUTO_INCREMENT=4 ;

-- 
-- Dumping data for table `categories`
-- 

INSERT INTO `categories` VALUES (1, 'Toyota');
INSERT INTO `categories` VALUES (2, 'Ford');
INSERT INTO `categories` VALUES (3, 'Mercedes');

Table 2 - cat_prod

-- Table structure for table `cat_prod`
-- 

CREATE TABLE `cat_prod` (
  `cid` int(11) NOT NULL,
  `pid` int(11) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_german2_ci;

-- 
-- Dumping data for table `cat_prod`
-- 

INSERT INTO `cat_prod` VALUES (1, 2);
INSERT INTO `cat_prod` VALUES (2, 1);
INSERT INTO `cat_prod` VALUES (3, 3);
INSERT INTO `cat_prod` VALUES (1, 4);
INSERT INTO `cat_prod` VALUES (1, 5);
INSERT INTO `cat_prod` VALUES (2, 6);

Table 3 - products

-- Table structure for table `products`
-- 

CREATE TABLE `products` (
  `pid` int(5) NOT NULL auto_increment,
  `prodName` varchar(255) collate latin1_german2_ci NOT NULL,
  PRIMARY KEY  (`pid`)
) ENGINE=MyISAM AUTO_INCREMENT=7 DEFAULT CHARSET=latin1 COLLATE=latin1_german2_ci AUTO_INCREMENT=7 ;

-- 
-- Dumping data for table `products`
-- 

INSERT INTO `products` VALUES (1, 'Focus');
INSERT INTO `products` VALUES (2, 'Scion');
INSERT INTO `products` VALUES (3, 'M-Class');
INSERT INTO `products` VALUES (4, 'CoolCar');
INSERT INTO `products` VALUES (5, 'RedCar');
INSERT INTO `products` VALUES (6, 'Blue Car');

[Adam]

Excellent, thanks ever so much! I shall try this a little later on...