CODEWITHSUNDEEP
mysqlsql
Dannyboy
Dannyboy

Reputation: 2052

Mysql how to do left join, group by and count only unique left column rows

I've got 2 tables:

CREATE TABLE `en_us` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `file_folder` varchar(6) DEFAULT NULL,
  `file_name` varchar(200) DEFAULT NULL,
  `file_type` varchar(200) DEFAULT NULL,
  `actual_word` varchar(200) DEFAULT NULL,
  `voice_type` tinyint(4) DEFAULT NULL,
  `note` text,
  `attention_bit` tinyint(4) DEFAULT '0',
  `no_file` tinyint(4) DEFAULT '1',
  `created` datetime DEFAULT NULL,
  `updated` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `file_name_location` (`file_name`,`file_type`,`file_folder`),
  KEY `file_name_idx` (`file_name`),
  KEY `actual_word_idx` (`actual_word`)
) ENGINE=InnoDB AUTO_INCREMENT=127961 DEFAULT CHARSET=utf8 |

CREATE TABLE `en_us_tags` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `fk_en_us_id` bigint(20) NOT NULL,
  `tag_text` varchar(200) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `tag_text_idx` (`tag_text`),
  KEY `fk_en_us_id` (`fk_en_us_id`),
  CONSTRAINT `en_us_tags_ibfk_1` FOREIGN KEY (`fk_en_us_id`) REFERENCES `en_us` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=139162 DEFAULT CHARSET=utf8

I need to do a left join joining en_us_tags to en_us and I need to get a count of unique en_us rows.

This query is wrong as it returns a count for each group instead: 

SELECT count(*) FROM audio.en_us 
LEFT JOIN audio.en_us_tags 
ON (audio.en_us.id = audio.en_us_tags.fk_en_us_id) 
GROUP BY (audio.en_us.id);

Help?

EDIT 1:(Rogue) That's mho wrong. I need total count.
and this is what get's outputted: 

mysql> SELECT DISTINCT COUNT(audio.en_us.id) FROM audio.en_us LEFT JOIN audio.en_us_tags ON audio.en_us.id = audio.en_us_tags.fk_en_us_id GROUP BY audio.en_us.id;
+-----------------------+
| COUNT(audio.en_us.id) |
+-----------------------+
|                     2 |
|                     1 |
|                     3 |
|                     4 |
|                     5 |
|                     6 |
|                     7 |
|                     9 |
+-----------------------+

EDIT 2: subquery approach works, but IMHO kinda slow as temp table will be constructed: 

SELECT COUNT(*) 
FROM (SELECT audio.en_us.id FROM audio.en_us 
LEFT JOIN audio.en_us_tags 
ON audio.en_us.id = audio.en_us_tags.fk_en_us_id 
GROUP BY audio.en_us.id) as test;
+----------+
| COUNT(*) |
+----------+
|   127960 |
+----------+
1 row in set (0.00 sec)

EDIT 3: So- does anyone know of a way to get a GROUPS count without using a subquery? As if one have 100,000 rows - subquery that creates temp table might be expensive.

Upvotes: 0

Views: 426

Answers (1)

Rogue
Rogue

Reputation: 11483

For a unique count of the rows, try something along the lines of:

SELECT COUNT(id)
FROM (
   SELECT DISTINCT *
   FROM audio.en_us
   LEFT JOIN audio.en_us_tags
   ON audo.en_us.id = audio.en_us_tags.fk_en_us_id
) t1;

This will only return the number of unique audio.en_us rows.

Upvotes: 1

Related Questions