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 9 of 9
  1. #1
    Regular Coder
    Join Date
    Apr 2003
    Location
    Montreal, QC
    Posts
    340
    Thanks
    3
    Thanked 2 Times in 2 Posts

    SELECTing records from a certain number of days

    Hi,
    I want to select records for several events in the future. Here's my problem - I need the next seven days that events occur on - but these days are not nessicarily consecutive.

    I had the idea to write a query like this:
    Code:
    SELECT 
    PROGRAMME.PROG_TITLE,
    COMPANY.CO_NAME,
    VENUE.VEN_CITY,
    PERFORMANCE.PERF_DATE_TIME,
    COUNT(DISTINCT DATE_FORMAT(PERFORMANCE.PERF_DATE_TIME,'%D-%m-%Y')) AS NUM_DAYS 
    FROM 
    PROGRAMME 
    JOIN COMPANY ON COMPANY.CO_ID = PROGRAMME.CO_ID 
    JOIN PERFORMANCE ON PERFORMANCE.PROG_ID = PROGRAMME.PROG_ID 
    JOIN VENUE ON VENUE.VEN_ID = PERFORMANCE.VEN_ID 
    WHERE NUM_DAYS <= 7 
    ORDER BY COMPANY.CO_NAME, PROG_DATE_TIME
    Only to realize that I can't use a column alias in the WHERE clause. I know that there must be an easy way to do this but I can't seem to figure out how. Please let me know! Thanks. John.
    Search for Laughter or Just Search?
    GiggleSearch.org
    Blog: www.johnbeales.com
    All About Ballet: www.the-ballet.com

  • #2
    Senior Coder
    Join Date
    Sep 2005
    Posts
    1,791
    Thanks
    5
    Thanked 36 Times in 35 Posts
    An inelegant (and untested...) way would be to repeat the alias-definition:
    ...WHERE (COUNT(DISTINCT DATE_FORMAT(PERFORMANCE.PERF_DATE_TIME,'%D-%m-%Y')))<7)...

  • #3
    Regular Coder
    Join Date
    Apr 2003
    Location
    Montreal, QC
    Posts
    340
    Thanks
    3
    Thanked 2 Times in 2 Posts
    Seems like a good idea but after trying it.....

    "Invalid use of group function"

    I guess we can't use COUNT in the WHERE clause.
    Search for Laughter or Just Search?
    GiggleSearch.org
    Blog: www.johnbeales.com
    All About Ballet: www.the-ballet.com

  • #4
    $object->toCD-R(LP); vinyl-junkie's Avatar
    Join Date
    Jun 2003
    Posts
    3,100
    Thanks
    2
    Thanked 23 Times in 23 Posts
    How about selecting one day at a time and incrementing some counter when you retrieve a non-zero result set for a particular day?
    Music Around The World - Collecting tips, trade
    and want lists, album reviews, & more
    SNAP to it!

  • #5
    Regular Coder
    Join Date
    Sep 2005
    Location
    Madison, Indiana, USA
    Posts
    166
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Maybe I'm not understanding your question, but it seems to me that this is what you want to do:
    Code:
    SELECT 
          PROGRAMME.PROG_TITLE,
          COMPANY.CO_NAME,
          VENUE.VEN_CITY,
          PERFORMANCE.PERF_DATE_TIME,
       FROM 
          PROGRAMME 
          JOIN COMPANY ON COMPANY.CO_ID = PROGRAMME.CO_ID 
          JOIN PERFORMANCE ON PERFORMANCE.PROG_ID = PROGRAMME.PROG_ID 
          JOIN VENUE ON VENUE.VEN_ID = PERFORMANCE.VEN_ID 
       ORDER BY COMPANY.CO_NAME, PROG_DATE_TIME
       LIMIT 7


    .

  • #6
    Regular Coder
    Join Date
    Apr 2003
    Location
    Montreal, QC
    Posts
    340
    Thanks
    3
    Thanked 2 Times in 2 Posts
    That's almost it hyperbole - the problem occurs when there is more than one performance on the same day - and I want to select a certain number of days. I need a way to limit the distinct values in one column.
    Search for Laughter or Just Search?
    GiggleSearch.org
    Blog: www.johnbeales.com
    All About Ballet: www.the-ballet.com

  • #7
    Regular Coder
    Join Date
    Jun 2005
    Posts
    804
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Try this:

    Code:
    SELECT 
     pr.prog_title
    ,c.co_name
    ,v.ven_city
    ,pe.perf_date_time 
    FROM 
     programme pr
    INNER JOIN
     company c
    ON c.co_id = pr.co_id
    INNER JOIN
     performance pe 
    ON
     pe.prog_id = pr.prog_id
    INNER JOIN
     venue v
    ON
     v.ven_id = pe.ven_id 
    WHERE 
     pe.perf_date_time <=
      (SELECT
        perf_date_time
       FROM
        performance
       WHERE
        perf_date_time <= DATE_ADD(CURDATE(), INTERVAL 7 DAY)
       ORDER BY
        perf_date_time DESC
       LIMIT 1)
    ORDER BY
     c.co_name
    ,pe.perf_date_time

  • #8
    Regular Coder
    Join Date
    Jun 2005
    Posts
    804
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Oops. You'll need to change

    Code:
    WHERE 
     pe.perf_date_time <=
      (SELECT
        perf_date_time
       FROM
        performance
       WHERE
        perf_date_time <= DATE_ADD(CURDATE(), INTERVAL 7 DAY)
       ORDER BY
        perf_date_time DESC
       LIMIT 1)
    to

    Code:
    WHERE 
     pe.perf_date_time BETWEEN CURDATE() AND
      (SELECT
        perf_date_time
       FROM
        performance
       WHERE
        perf_date_time <= DATE_ADD(CURDATE(), INTERVAL 7 DAY)
       ORDER BY
        perf_date_time DESC
       LIMIT 1)
    or you'll get events that have already evented. Sorry 'bout that.

  • #9
    Regular Coder
    Join Date
    Apr 2003
    Location
    Montreal, QC
    Posts
    340
    Thanks
    3
    Thanked 2 Times in 2 Posts
    It looks great - except for one thing - my host is currently only running MySQL version 4.0.25 - this means I can't use subqueries. I suppose I should have mentioned this earlier.

    I have, I think found a solution though.....

    Code:
    SELECT 
    PROGRAMME.PROG_TITLE, 
    COMPANY.CO_NAME, 
    VENUE.VEN_CITY, 
    PERFORMANCE.PERF_ID, 
    PERFORMANCE.PERF_DATE_TIME,  
    CIVIL_TIME.CT_UTC_OFFSET,
    CIVIL_TIME_YEAR.CTY_DST_OFFSET, 
    COUNT(DISTINCT DATE_FORMAT(PERFORMANCE.PERF_DATE_TIME,'%D-%m-%Y')) AS NUM_DAYS
    FROM PROGRAMME 
    JOIN COMPANY ON COMPANY.CO_ID = PROGRAMME.CO_ID
    JOIN PERFORMANCE ON PERFORMANCE.PROG_ID = PROGRAMME.PROG_ID
    JOIN VENUE ON VENUE.VEN_ID = PERFORMANCE.VEN_ID 
    JOIN CIVIL_TIME ON CIVIL_TIME.CT_ID = VENUE.CT_ID
    LEFT JOIN CIVIL_TIME_YEAR ON CIVIL_TIME_YEAR.CT_ID = CIVIL_TIME.CT_ID 
    AND (
    (CIVIL_TIME_YEAR.CTY_DST_START < DATE_ADD( '1970-01-01', INTERVAL UNIX_TIMESTAMP() SECOND ) AND CIVIL_TIME_YEAR.CTY_DST_END > CIVIL_TIME_YEAR.CTY_DST_START)
    OR
    (DATE_ADD( '1970-01-01', INTERVAL UNIX_TIMESTAMP() SECOND ) > CIVIL_TIME_YEAR.CTY_DST_START AND CIVIL_TIME_YEAR.CTY_DST_END < CIVIL_TIME_YEAR.CTY_DST_START)
    ) 
    GROUP BY PERFORMANCE.PERF_ID 
    HAVING NUM_DAYS<=7 AND PERFORMANCE.PERF_DATE_TIME > NOW()
    ORDER BY COMPANY.CO_ID, PERFORMANCE.PERF_DATE_TIME
    The CIVIL_TIME and CIVIL_TIME_YEAR tables are part of my own proprietary timezone management system, (only running PHP 4.3 - so little to no TZ support), however the GROUP BY and HAVING together appear to be getting me the results I want.

    There may be more to come.

    John
    Search for Laughter or Just Search?
    GiggleSearch.org
    Blog: www.johnbeales.com
    All About Ballet: www.the-ballet.com


  •  

    Posting Permissions

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