Hello and welcome to our community! Is this your first visit?
Register
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
    Posts
    96
    Thanks
    11
    Thanked 0 Times in 0 Posts

    Linking tables to look for records that aren't there

    Hi,

    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:

    [CODE]

    $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 . "'");
    [CODE]

    But I just get a syntax error
    Last edited by icklechurch; 06-21-2010 at 02:35 PM.

  • #2
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,987
    Thanks
    4
    Thanked 2,660 Times in 2,629 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 >.<
    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
    Also, I cannot help but notice the square brackets. Are you sure this is mysql; its looking more like SQLServer or Access then MySQL.

    Edit:
    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'); 

  • #3
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,216
    Thanks
    75
    Thanked 4,344 Times in 4,310 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.
    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.

  • #4
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,633
    Thanks
    4
    Thanked 148 Times in 139 Posts
    icklechurch

    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
    •