[SOLVED] LEFT JOINs and COUNT

[discomatt]

I'm having a really odd problem with a query I'm trying to buildI'm in no way an SQL guru, so if it's somthing known or obvious, please be gentle

 

Here's the structure and some sample data for two tables I'm trying to interact with.

 

-- 
-- Table structure for table `data`
-- 

CREATE TABLE `data` (
  `id` int(11) NOT NULL auto_increment,
  `content` varchar(50) NOT NULL,
  `uid` tinyint(3) unsigned NOT NULL,
  UNIQUE KEY `id` (`id`),
  KEY `uid` (`uid`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=7 ;

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

INSERT INTO `data` (`id`, `content`, `uid`) VALUES 
(1, 'Sample Data', 1),
(2, 'Sample Data', 1),
(3, 'Sample Data', 1),
(4, 'Sample Data', 2),
(5, 'Sample Data', 3),
(6, 'Sample Data', 3);

-- --------------------------------------------------------

-- 
-- Table structure for table `users`
-- 

CREATE TABLE `users` (
  `id` tinyint(3) unsigned NOT NULL auto_increment,
  `name` varchar(15) NOT NULL,
  UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;

-- 
-- Dumping data for table `users`
-- 

INSERT INTO `users` (`id`, `name`) VALUES 
(1, 'user1'),
(2, 'user2'),
(3, 'user3'),
(4, 'user4');

 

Here's my query, which outputs the kind of data I'd expect it to.

 

mysql> SELECT users.name, COUNT(data.id) AS total
    -> FROM users LEFT JOIN data
    -> ON users.id = data.uid
    -> GROUP BY data.uid
    -> ORDER BY users.name;
+-------+-------+
| name  | total |
+-------+-------+
| user1 |     3 |
| user2 |     1 |
| user3 |     2 |
| user4 |     0 |
+-------+-------+
4 rows in set (0.00 sec)

 

But if we empty the `data` table

mysql> TRUNCATE TABLE data;
Query OK, 6 rows affected (0.05 sec)

 

And perform the query again

mysql> SELECT users.name, COUNT(data.id) AS total
    -> FROM users LEFT JOIN data
    -> ON users.id = data.uid
    -> GROUP BY data.uid
    -> ORDER BY users.name;
+-------+-------+
| name  | total |
+-------+-------+
| user1 |     0 |
+-------+-------+
1 row in set (0.00 sec)

 

It only returns one user?! Ideally, the results would be

+-------+-------+
| name  | total |
+-------+-------+
| user1 |     0 |
| user2 |     0 |
| user3 |     0 |
| user4 |     0 |
+-------+-------+

 

Is there something wrong with my logic here? Or is what I'm asking impossible?

[discomatt]

Well, I've solved my own problem... changing

 

GROUP BY data.uid

to

GROUP BY user.id

 

My guess is it has to do with doing then join and THEN grouping...

[Hooker]

i was about to post congrats on working it out yourself.

[discomatt]

Thanks... I'm going to have to look up more details on how the MySQL engine actually works - might save me from running into silly problems like this again.

[fenway]

Thanks... I'm going to have to look up more details on how the MySQL engine actually works - might save me from running into silly problems like this again.

LEFT JOIN "nulls out" any columns in non-matching rows, so you were grouping by NULL.