Why does this SELECT ... JOIN statement return no results?
- by Stephen
I have two tables:
1. tableA is a list of records with many columns. There is a timestamp column called "created"
2. tableB is used to track users in my application that have locked a record in tableA for review. It consists of four columns: id, user_id, record_id, and another timestamp collumn.
I'm trying to select up to 10 records from tableA that have not been locked by for review by anyone in tableB (I'm also filtering in the WHERE clause by a few other columns from tableA like record status). Here's what I've come up with so far:
SELECT tableA.* FROM tableA
LEFT OUTER JOIN tableB ON tableA.id = tableB.record_id WHERE
tableB.id = NULL AND
tableA.status = 'new' AND
tableA.project != 'someproject' AND
tableA.created BETWEEN '1999-01-01 00:00:00' AND '2010-05-06 23:59:59'
ORDER BY tableA.created ASC LIMIT 0, 10;
There are currently a few thousand records in tableA and zero records in tableB. There are definitely records that fall between those timestamps, and I've verified this with a simple
SELECT * FROM tableA WHERE
created BETWEEN '1999-01-01 00:00:00' AND '2010-05-06 23:59:59'
The first statement above returns zero rows, and the second one returns over 2,000 rows.
