Trouble limiting results by date in WHERE clause

Databases
1

I’m trying to get the number of nodes created in the last six months by users in a group who do not have “example.com” in their email address.

I think I have most of the query working but the “created in the last six months” part is not … I’m guessing it’s a problem with my join syntax. It doesn’t matter what I change the INTERVAL value to (6 MONTH or 6 DAY), I get the same count.

mysql> SELECT COUNT(*)
    -> FROM node n
    -> LEFT JOIN users u
    -> ON n.uid = u.uid
    -> LEFT OUTER JOIN og_uid og
    -> ON u.uid = og.uid
    -> LEFT JOIN node nog
    -> ON og.nid = nog.nid
    -> WHERE nog.nid = 858031
    -> AND u.mail NOT LIKE '%example.com%'
    -> AND n.created >= DATE_SUB(CURDATE(), INTERVAL 6 MONTH);
+----------+
| COUNT(*) |
+----------+
|    18637 | 
+----------+
1 row in set (0.11 sec)

mysql> DESCRIBE node;
+-----------+------------------+------+-----+---------+----------------+
| Field     | Type             | Null | Key | Default | Extra          |
+-----------+------------------+------+-----+---------+----------------+
| nid       | int(10) unsigned | NO   | PRI | NULL    | auto_increment | 
| type      | varchar(32)      | NO   | MUL |         |                | 
| title     | varchar(255)     | NO   | MUL |         |                | 
| uid       | int(10)          | NO   | MUL | 0       |                | 
| status    | int(4)           | NO   | MUL | 1       |                | 
| created   | int(11)          | NO   | MUL | 0       |                | 
| changed   | int(11)          | NO   | MUL | 0       |                | 
| comment   | int(2)           | NO   |     | 0       |                | 
| promote   | int(2)           | NO   | MUL | 0       |                | 
| moderate  | int(2)           | NO   | MUL | 0       |                | 
| sticky    | int(2)           | NO   |     | 0       |                | 
| vid       | int(10) unsigned | NO   | UNI | 0       |                | 
| language  | varchar(12)      | NO   |     |         |                | 
| tnid      | int(10) unsigned | NO   | MUL | 0       |                | 
| translate | int(11)          | NO   | MUL | 0       |                | 
+-----------+------------------+------+-----+---------+----------------+
15 rows in set (0.00 sec)

mysql> DESCRIBE users;
+------------------+------------------+------+-----+---------+----------------+
| Field            | Type             | Null | Key | Default | Extra          |
+------------------+------------------+------+-----+---------+----------------+
| uid              | int(10) unsigned | NO   | PRI | NULL    | auto_increment | 
| name             | varchar(60)      | NO   | UNI |         |                | 
| pass             | varchar(32)      | NO   |     |         |                | 
| mail             | varchar(64)      | YES  | MUL |         |                | 
| mode             | tinyint(1)       | NO   |     | 0       |                | 
| sort             | tinyint(1)       | YES  |     | 0       |                | 
| threshold        | tinyint(1)       | YES  |     | 0       |                | 
| theme            | varchar(255)     | NO   |     |         |                | 
| signature        | varchar(255)     | NO   |     |         |                | 
| created          | int(11)          | NO   | MUL | 0       |                | 
| access           | int(11)          | NO   | MUL | 0       |                | 
| status           | tinyint(4)       | NO   | MUL | 0       |                | 
| timezone         | varchar(8)       | YES  |     | NULL    |                | 
| language         | varchar(12)      | NO   |     |         |                | 
| picture          | varchar(255)     | NO   |     |         |                | 
| init             | varchar(64)      | YES  |     |         |                | 
| data             | longtext         | YES  |     | NULL    |                | 
| login            | int(11)          | NO   |     | 0       |                | 
| timezone_name    | varchar(50)      | NO   |     |         |                | 
| signature_format | smallint(6)      | NO   |     | 0       |                | 
+------------------+------------------+------+-----+---------+----------------+
20 rows in set (0.00 sec)

mysql> DESCRIBE og_uid;
+-----------+---------+------+-----+---------+-------+
| Field     | Type    | Null | Key | Default | Extra |
+-----------+---------+------+-----+---------+-------+
| nid       | int(11) | NO   | PRI | 0       |       | 
| uid       | int(11) | NO   | PRI | 0       |       | 
| og_role   | int(1)  | NO   |     | 0       |       | 
| is_active | int(1)  | YES  |     | 0       |       | 
| is_admin  | int(1)  | YES  |     | 0       |       | 
| created   | int(11) | YES  |     | 0       |       | 
| changed   | int(11) | YES  |     | 0       |       | 
+-----------+---------+------+-----+---------+-------+
7 rows in set (0.00 sec)

mysql>
2

I left out a table description:

mysql> DESCRIBE og;
+----------------+--------------+------+-----+---------+-------+
| Field          | Type         | Null | Key | Default | Extra |
+----------------+--------------+------+-----+---------+-------+
| nid            | int(11)      | NO   | PRI | 0       |       | 
| og_selective   | int(11)      | NO   |     | 0       |       | 
| og_description | varchar(255) | YES  |     | NULL    |       | 
| og_theme       | varchar(255) | YES  |     | NULL    |       | 
| og_register    | tinyint(4)   | NO   |     | 0       |       | 
| og_directory   | tinyint(4)   | NO   |     | 0       |       | 
| website        | varchar(255) | YES  |     | NULL    |       | 
| og_language    | varchar(12)  | NO   |     |         |       | 
| og_private     | tinyint(4)   | NO   |     | 0       |       | 
+----------------+--------------+------+-----+---------+-------+
9 rows in set (0.00 sec)

mysql>
3

the problem with using COUNT(*) at the same time as LEFT OUTER JOINs is that whether there’s a match or not, there’s still an output row, so the count is the same

here’s a join result with 4 matching rows and 3 not matching

one 1
two NULL
thr NULL
for 4
fiv 5
six NULL
svn 7
------ COUNT(*) = 7

here’s a join result with 6 matching rows and 1 not matching

one 1
two NULL
thr 3
for 4
fiv 5
six 6
svn 7
------ COUNT(*) = 7

can you explain why you are joining each of the tables in your query? what are they for? and what exactly is it that you want to count?

4

Sure r937,

I’m trying to count the number of articles (nodes) submitted by authors (users) who belong to group (og) #858031 in the last six months and do not work for example.com.

The query gets a little wonky because both articles and groups are stored in the node table which hopefully explains the joins between og (organic groups), nodes, and users.

SELECT COUNT(*)
-> FROM node n
-> LEFT JOIN users u
-> ON n.uid = u.uid
-> LEFT OUTER JOIN og_uid og
-> ON u.uid = og.uid
-> LEFT JOIN node nog
-> ON og.nid = nog.nid
-> WHERE nog.nid = 858031
-> AND u.mail NOT LIKE '%example.com%'
-> AND n.created >= DATE_SUB(CURDATE(), INTERVAL 6 MONTH);
5

your joins should all be INNER JOINs

SELECT COUNT(*)
  FROM node AS n
INNER
  JOIN users AS u
    ON u.uid = n.uid
   AND u.mail NOT LIKE '%example.com%'
INNER
  JOIN og_uid AS og
    ON og.uid = u.uid
   AND og.nid = 858031
INNER
  JOIN node AS nog
    ON nog.nid = og.nid
 WHERE n.created >= [COLOR="Blue"]UNIX_TIMESTAMP(CURRENT_DATE - INTERVAL 6 MONTH)[/COLOR]
6

Fantastic!
Thanks r937!!