Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 4 of 4
06-21-2010, 02:23 PM #1
- Join Date
- Mar 2009
- Thanked 0 Times in 0 Posts
Linking tables to look for records that aren't there
I have an odd query I want to write.
Basically, I have 2 tables. One called activity where everytime a record is viewed it is added to the activity table. Another which is just a list of all the records in the database.
What I want is to find all records in the record table that do not appear in the activity table (i.e. the ones which have not been viewed). The records are distinguishable by the column record_id, which appears in both tables.
Can someone point me in the right direction to do this please?
At the moment I have:
$ActivityDatum = SQLGet("SELECT records.[record_id]
RIGHT JOIN records ON activity.record_id = records.[record_id]
WHERE date_accessed <= '" . $date_to . "' AND date_accessed >= '" . $date_from . "'");
But I just get a syntax error
Last edited by icklechurch; 06-21-2010 at 02:35 PM.
06-21-2010, 03:29 PM #2
- Join Date
- Sep 2002
- Saskatoon, Saskatchewan
- Thanked 2,659 Times in 2,628 Posts
Lets see if I can get it right this time
OldPedant will pop in later I'm sure to correct it if I'm wrong >.<
Also, I cannot help but notice the square brackets. Are you sure this is mysql; its looking more like SQLServer or Access then MySQL.Code:SELECT r.record_id FROM records r LEFT JOIN activities a ON a.record_id = r.record_id WHERE a.record_id IS NULL
Last edited by Fou-Lu; 06-21-2010 at 03:31 PM.
header('HTTP/1.1 420 Enhance Your Calm');
06-21-2010, 07:28 PM #3
Agree that FouLu's rewrite makes logic easier to see and understand.
But if the problem is not the square brackets, then I don't see why the syntax error from the original query.
So also have to wonder if this is MySQL or some other DB.
An optimist sees the glass as half full.
A pessimist sees the glass as half empty.
A realist drinks it no matter how much there is.
06-22-2010, 03:09 PM #4
- Join Date
- Mar 2006
- St. Catharines, Ontario Canada
- Thanked 147 Times in 138 Posts
When asking for help don't merely state that you get a syntax error. Please post the exact syntax error message you get. Then people don't have to guess.