Hi,
Been working with mysql for a while now but need some help with joins.

Can I do joins on subqueries?
I have one table - users
+----------+------------+------+
| Field    | Type       | Null |
+----------+------------+------+
| userID   | int(11)    | NO   |
| name     | varchar(5) | YES  |
| location | tinyint(1) | YES  |
| isActive | tinyint(1) | YES  |
| gender   | char(1)    | YES  |
+----------+------------+------+

I wish to get the count of users by location optionally filtred by any of the other fields. I also wish to display zero(0) if the criteria is not met.

Here's the typical output I expect.
+------+----------+------+
| id   | location | hits |
+------+----------+------+
| 0    | 0        | 4    |
| 0    | 2        | 2    |
| 0    | 3        | 3    |
| 0    | 4        | 4    |
| 0    | 5        | 2    |
| 1    | 0        | 3    |
| 1    | 2        | 4    |
| 1    | 3        | 2    |
| 1    | 4        | 4    |
| 1    | 5        | 2    |
+------+----------+------+

Here's my query:
SELECT q.isActive as id, q.location, count(userID) as hits FROM (SELECT distinct s.isActive, p.location FROM (SELECT distinct isActive FROM users) as s JOIN (SELECT DISTINCT location FROM users) as p ORDER BY isActive, location) as q LEFT JOIN (SELECT * FROM users) AS u ON (q.isActive = u.isActive AND q.location = u.location) GROUP BY q.isActive, q.location;

So far so good. The problem occurs when I introduce a WHERE clause - my LEFT JOIN seems to be discarded therefore I don't have a record for each id-location pair.


SELECT q.isActive, q.location, count(userID) as hits FROM (SELECT distinct s.isActive, p.location FROM (SELECT distinct isActive FROM users) as s JOIN (SELECT DISTINCT location FROM users) as p ORDER BY isActive, location) as q LEFT JOIN (SELECT * FROM users) AS u ON (q.isActive = u.isActive AND q.location = u.location) WHERE u.gender = 'F' GROUP BY q.isActive, q.location;
+----------+----------+------+
| isActive | location | hits |
+----------+----------+------+
| 0        | 0        | 1    |
| 0        | 3        | 3    |
| 0        | 4        | 1    |
| 0        | 5        | 1    |
| 1        | 0        | 2    |
| 1        | 2        | 2    |
| 1        | 3        | 2    |
| 1        | 4        | 2    |
| 1        | 5        | 2    |
+----------+----------+------+
(1 row is missing - where location=2 and isActive=0). How do I ensure it always shows?

SQL create statements with test data below:
CREATE TABLE `users` (
 `userID` int(11) NOT NULL auto_increment,
 `name` varchar(5) default NULL,
 `location` tinyint(1) default NULL,
 `isActive` tinyint(1) default NULL,
 `gender` char(1) default NULL,
 PRIMARY KEY  (`userID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
INSERT INTO `users` VALUES (3,'uid3',0,1,'M'),(148,'uid14',4,0,'F'),(149,'uid14',4,1,'M'),(150,'uid15',5,0,'F'),
(143,'uid14',4,1,'F'),(154,'uid15',0,0,'M'),(152,'uid15',5,0,'M'),(153,'uid15',5,1,'F'),
(151,'uid15',5,1,'F'),(147,'uid14',0,1,'F'),(146,'uid14',4,0,'M'),(139,'uid13',3,1,'F'),
(140,'uid14',0,0,'F'),(141,'uid14',4,1,'F'),(168,'uid16',0,0,'M'),(145,'uid14',4,1,'M'),
(144,'uid14',4,0,'M'),(142,'uid14',4,0,'M'),(123,'uid12',2,1,'M'),(124,'uid12',2,0,'M'),
(125,'uid12',2,1,'F'),(126,'uid12',0,0,'M'),(127,'uid12',2,1,'M'),(128,'uid12',2,0,'M'),
(129,'uid12',2,1,'F'),(130,'uid13',3,0,'F'),(131,'uid13',3,1,'F'),(132,'uid13',3,0,'F'),
(133,'uid13',0,1,'F'),(134,'uid13',3,0,'F');


It look to me at a first brief glance that your left join has worked correctly and produced some records with NULL u.gender fields.  These are then eliminated correctly by the WHERE clause that you've used so that the effect of the left join (i.e. the extra records it brought in over a normal join) is all removed.

Does that make sense and point you to a way to alter your logic to produce what you really need it to do?

Thanks.

What I really want to do is retain the nulls and at the same time use a WHERE clause so that the first 2 columns always remain the same for each query but the hits vary depending on the WHERE clause.

I think you have a problem with the logic  

You COULD do what you want by adding "or u.gender is NULL" to the where clause, but I think that would just lead you to making a design issue worse and trying to right code that doesn't fix the underlying problem but just papers over the cracks.

OK Maybe I'll review my logic.

I was under the impression that with the LEFT JOIN I can fix one part of the result set and vary the other side. After all if I really didn't want the NULLs I could explicitly say so on my WHERE clause.

I tried the OR gender is NULL already. Same difference!

Thanks man. I just got the hack. I moved the WHERE part to the ON clause i.e.
AS u ON (q.isActive = u.isActive AND q.location = u.location AND u.gender = 'F')

---