CODEWITHSUNDEEP
mysqlsqljoincount
Rubytastic
Rubytastic

Reputation: 15491

newbie: mysql join 2 tables to gether and count the results

Im trying to JOIN 2 tables and count the results. I have these tables;

SET NAMES utf8;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
--  Table structure for `segments`
-- ----------------------------
DROP TABLE IF EXISTS `segments`;
CREATE TABLE `segments` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `substr_id` int(255) NOT NULL,
  `substr` varchar(255) NOT NULL,
  `count` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;

-- ----------------------------
--  Records of `segments`
-- ----------------------------
BEGIN;
INSERT INTO `segments` VALUES ('1', '1', 'book', '2'), ('2', '2', 'ooki', '1'), ('3', '2', 'okin', '1'), ('4', '2', 'king', '1');
COMMIT;

-- ----------------------------
--  Table structure for `words`
-- ----------------------------
DROP TABLE IF EXISTS `words`;
CREATE TABLE `words` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `substr_id` int(11) NOT NULL,
  `word` varchar(255) NOT NULL,
  `cleaned` tinyint(1) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;

-- ----------------------------
--  Records of `words`
-- ----------------------------
BEGIN;
INSERT INTO `words` VALUES ('1', '1', 'book', '0'), ('2', '2', 'booking', '0'), ('3', '2', 'booking', '0'), ('4', '2', 'booking', '0');
COMMIT;

SET FOREIGN_KEY_CHECKS = 1;

Changed question with sql + data.

Im trying to :

 SELECT words with there count from segments.count based upon there substr_id.

Anyone could help me get this done? Im not that good at SQL have red docs and some tutorials but don't quite get how I should do all of above in a single query.

Upvotes: 0

Views: 174

Answers (3)

Kreg
Kreg

Reputation: 647

You'd probably want a join condition of some kind, otherwise you'll get a cartesian product, but this this should get you started:

SELECT COUNT(*)
FROM words
JOIN count --add an ON condition here unless you want a cartesian product

it's a little unclear what you're looking to do, but feel free to clarify if this isn't quite what you're looking for.

Good luck!

Upvotes: 1

User1000547
User1000547

Reputation: 4301

 SELECT word, count
   FROM words NATURAL JOIN segments;

Should do the trick. I'd recommend doing some more research. http://en.wikipedia.org/wiki/Join_(SQL)

Upvotes: 2

Josh
Josh

Reputation: 7

You need the aggregate function COUNT() in order to count results of a query.

There are a number of links that can help with this, but something like this http://dev.mysql.com/doc/refman/5.1/en/counting-rows.html should be of assistance.

Upvotes: 1

Related Questions