1

I have two tables as follows:

tasks:

id int(5) auto_increment,
content mediumtext,
primary key(id)

completed:

id int(10) auto_increment,
taskid int(5),
datetime int(11),
primary key(id)

I'm attempting to run an SQL query to pull out the tasks that have not been marked as completed today. I'm doing the time calculation from PHP, so the query itself looks something like this:

$morning = mktime(0, 0, 0);
$night = mktime(23, 59, 59);
$query = sprintf("SELECT t.id, t.content FROM tasks as t, completed as c WHERE c.datetime < %s AND c.datetime > %s AND t.id != c.taskid", $night, $morning);

This comes out something like the following:

SELECT t.id, t.content FROM tasks as t, completed as c WHERE c.datetime < 1391471999 AND c.datetime > 1391385600 AND t.id != c.taskid

If someone could point me in the correct direction, that would be awesome. Thanks :)

Andrew White
  • 600
  • 1
  • 9
  • 29
  • Consider providing proper DDLs (and/or an sqlfiddle) TOGETHER WITH THE DESIRED RESULT SET – Strawberry Feb 03 '14 at 02:04
  • Thanks, I wasn't aware of this tool. Here's the schema and some sample queries based on Michal's answer. http://sqlfiddle.com/#!2/d8a357/6 If I could get the SQL to execute with the desired result set, I wouldn't be here ;). The desired set is all tasks that do not exist in the completed table on the current day. – Andrew White Feb 03 '14 at 09:07

2 Answers2

3

Using comma operator is INNER JOINing tables. But you are trying to get tasks, which have no corresponding action in the completed table so you should use LEFT JOIN instead to get those. You should also use MySQL datetime comparison as it is less code writting and you don't have to count in timezones all the time.

I suppose you have no tasks with future date in table completed.

SELECT    t.id, t.content
FROM      tasks as t
LEFT JOIN completed as c ON t.id = c.taskid
WHERE     c.datetime < 1391385600
          OR c.datetime >= 1391472000
          OR c.taskid IS NULL

Select tasks, which:

  • were completed before today 1391385600
  • or were completed in future 1391472000
  • or are not completed at all
Community
  • 1
  • 1
Michal Brašna
  • 2,293
  • 13
  • 17
  • Thanks for your response Michal, however this doesn't appear to have the effect I want. To clarify, I wish to pull a list of the tasks that have not been completed (so a full list of things in the tasks table, minus the ones that appear in the completed table with a datetime matching today). This left join appears to do the opposite - only pull them if they are completed. Please note that I got this response after removing the "And c.taskid IS NULL" - with this on the end, I got an empty set back. – Andrew White Feb 03 '14 at 08:58
  • Here's an SQL fiddle explaining my attempts with your sample! :) http://sqlfiddle.com/#!2/d8a357/6 – Andrew White Feb 03 '14 at 09:07
  • I updated the query. I haven't noticed the int(11) type of datetime. Also fixed brackets and operator, which was bad typo. Also my example in your code http://sqlfiddle.com/#!2/d8a357/13 – Michal Brašna Feb 03 '14 at 09:13
  • Oh nice. Now it appears to pull the whole table from tasks, so definitely getting better! I added t.taskid to the SELECT and if they're NULL or if they have a taskid they still return. I'm wondering with the "c.datetime >= 1391385600 AND c.datetime < 1391472000 OR c.taskid IS NULL" - doesn't this say "if it's in the right date range OR if it the taskid is NULL"? Shouldn't it be both? If we have have no rows (meaning a NULL) within the correct datetimes? – Andrew White Feb 03 '14 at 09:22
  • I tweaked it little bit more and added explanation. Also updated http://sqlfiddle.com/#!2/d8a357/16 – Michal Brašna Feb 03 '14 at 09:31
  • LEFT JOIN x WHERE x.m = 'n' isn't going to work. That renders as an INNER JOIN, which makes a nonsense of `x.n IS NULL` – Strawberry Feb 03 '14 at 10:41
  • Thanks Michal, your answer was great and appreciated! – Andrew White Feb 03 '14 at 11:13
1

I don't really understand. The data set is too small to be representative. And if you can't figure out what the desired result set would look like then you probably shouldn't be here ;-).

That said, is this what you're after...

SELECT t.* 
  FROM tasks t
  LEFT
  JOIN completed c
    ON c.taskid = t.id
   AND c.datetime BETWEEN  1391385600 AND  1391472000
 WHERE c.id IS NULL;

?

Strawberry
  • 33,750
  • 13
  • 40
  • 57