MySQL Syntax Statement

Discussion in 'Web Design and Development' started by Cerebrus' Maw, Jul 18, 2011.

  1. Cerebrus' Maw macrumors 6502

    Joined:
    Mar 9, 2008
    Location:
    Brisbane, Australia
    #1
    Hi guys. Just struggling on wrapping my head around a MySQL statement I need for unique records.

    I have a table that will be queried by a date range. IE-> return records between 2011-07-20 and 2011-07-31. Now imagine these records have a field called _ID

    What complicates it is that it must ignore records that have a same _ID outside of the date range.

    EG. Running a query with the above dates, and it returns 10 records with _ID 1-10. But three of these records, lets say 6,7,8 occur before the date range. The query has to ignore these records with _ID 6,7,8.

    The PHP programmer in me see's the easy solution of just running two queries and looping on an exclude basis, but this table will get rather big fast, so would prefer as little mysql overhead as possible....
     
  2. bpaluzzi macrumors 6502a

    bpaluzzi

    Joined:
    Sep 2, 2010
    Location:
    London
    #2
    Not quite following it -- can you describe your schema, and what you're trying to do in a little more detail?

    Cheers,
    b
     
  3. Cerebrus' Maw thread starter macrumors 6502

    Joined:
    Mar 9, 2008
    Location:
    Brisbane, Australia
    #3
    No worries. A table with some data.

    Date | _ID | Name |
    ===================
    2011-07-05 | 1 | Alan |
    2011-07-09 | 6 | Paul |
    2011-07-10 | 2 | Steve |
    2011-07-18 | 9 | James|
    2011-07-19 | 6 | Rick |
    2011-07-20 | 5 | Peter |

    Now, I do a search for records between 2011-07-18 to 2011-07-20. It should only return James and Peter, as Rick has an _ID of 6, which exists earlier in the table, even though my date range does not include this.

    Does that clear it up? Just to let you know, I did manage to figure it out, but I'll let it up as a challenge ;) As a bonus it should also exclude if the _ID exists after the date rage specified. So if a record as such:

    2011-07-25 | 9 | Henry

    then only Peter should return, as James and Henry have the same _ID (9)
     
  4. Thom_Edwards macrumors regular

    Joined:
    Apr 11, 2003
    #4
    Code:
    SELECT * FROM yourTable
    WHERE Date BETWEEN 2011-07-18 AND 2011-07-20
    AND _ID NOT IN (SELECT _ID FROM yourTable
    WHERE Date NOT BETWEEN 2011-07-18 AND 2011-07-20)
    
    This is semi-pseudocode, meaning the syntax is not spot on, but the spirit of it should get you there.

    Now, what do I win? ;-)
     
  5. Cerebrus' Maw thread starter macrumors 6502

    Joined:
    Mar 9, 2008
    Location:
    Brisbane, Australia
    #5
    Ding Ding, Winner!

    Pretty much exactly how I did, although I did tool around with EXISTS and NOT EXISTS before settling on NOT IN. I've got a feeling that I might be coming back to this thread, as the project I'm working on has MySQL optimization as a priority, and I aint that much of a gun on SQL languages.

    Prize? Uh....I must have.....left it outside.... in my car......Back in a jiffy!
     

Share This Page