Hello and welcome to our community! Is this your first visit?
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 4 of 4
  1. #1
    New Coder
    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]
    FROM activity
    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.

  2. #2
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Saskatoon, Saskatchewan
    Thanked 2,668 Times in 2,637 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 >.<
    SELECT r.record_id
    FROM records r
    LEFT JOIN activities a ON a.record_id = r.record_id
    WHERE a.record_id IS NULL
    Also, I cannot help but notice the square brackets. Are you sure this is mysql; its looking more like SQLServer or Access then MySQL.

    Oh yeah and sorry missed the other parts of your where conditions. Thats ok, they can also just be added to the where with an AND.
    Last edited by Fou-Lu; 06-21-2010 at 03:31 PM.
    PHP Code:
    header('HTTP/1.1 420 Enhance Your Calm'); 
    Been gone for a few months, and haven't programmed in that long of a time. Meh, I'll wing it ;)

  3. #3
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Thanked 4,947 Times in 4,908 Posts
    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.
    Be yourself. No one else is as qualified.

  4. #4
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    St. Catharines, Ontario Canada
    Thanked 156 Times in 147 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.


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts