SitePoint Sponsor

User Tag List

Results 1 to 10 of 10
  1. #1
    SitePoint Enthusiast
    Join Date
    Oct 2001
    Location
    Helston, Cornwall, England
    Posts
    76
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Calendar select problem

    Hi all,

    MySQL Ver. 4.0.25.

    I am tinkering with a calendar script I have on my site using the following table.

    ----------------------------------------------
    id(int11) | status(int1) | ddate(date)
    ----------------------------------------------
    45 | 1 | 2006-02-01
    45 | 1 | 2006-02-02
    45 | 1 | 2006-02-03
    45 | 3 | 2006-02-04
    45 | 3 | 2006-02-05
    45 | 3 | 2006-02-06
    45 | 3 | 2006-02-07
    45 | 1 | 2006-02-08

    I want to be able to select only the dates where status 3 is preceeded by 1 and followed by 1. In the case above that would return 2 results 2006-02-04 and 2006-02-07.

    Any advice gratefully accepted to stop me just staring blankly at the screen.

  2. #2
    SitePoint Wizard Ren's Avatar
    Join Date
    Aug 2003
    Location
    UK
    Posts
    1,060
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    SELECT t3.* FROM table AS t3
    INNER JOIN table AS t1p ON t3.ddate > t1p.ddate
    LEFT JOIN table AS i ON i.ddate > t1p.ddate AND i.ddate < t3.ddate
    WHERE t3.status = 3 and t1p.status = 1 AND i.ddate IS NULL

    I think is one half of this problem, first join selects the previous status 1 row, and left join involving i, makes sure there is no in between rows.

    Using a UNION with a similar query will get the rows that are followed by a status 1

  3. #3
    SitePoint Enthusiast
    Join Date
    Oct 2001
    Location
    Helston, Cornwall, England
    Posts
    76
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi Ren,

    Thanks for the help, though I keep getting an error when running it.

    right syntax to use near ''tbl_availability' AS t3 INNER JOIN 'tbl_availability' AS t1p
    The query including tablename is below

    SELECT t3.* FROM 'tbl_availability' AS t3 INNER JOIN 'tbl_availability' AS t1p ON t3.ddate > t1p.ddate LEFT JOIN 'tbl_availability' AS i ON i.ddate > t1p.ddate AND i.ddate < t3.ddate WHERE t3.status = '3' and t1p.status = '1' AND i.ddate IS NULL AND t3.property_id = '45'

    Can you see where I'm going wrong?

    I take it that to return the result I pull out t3.ddate?

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Ward
    Can you see where I'm going wrong?
    yep, you're using character strings instead of table names

    FROM 'tbl_availability' /* character string */

    FROM tbl_availability /* table name */

    by the way, you're not going to have much luck solving this query without subqueries (requires version 4.1)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Enthusiast
    Join Date
    Oct 2001
    Location
    Helston, Cornwall, England
    Posts
    76
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    thanks r937,

    I was testing in phpMyAdmin and replaced the `` for '' doh. I guess I'm stuck until my host upgrades MySQL then?

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Ward
    I guess I'm stuck until my host upgrades MySQL then?
    no, you can still solve your problem, you just can't do it with one query
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Wizard Ren's Avatar
    Join Date
    Aug 2003
    Location
    UK
    Posts
    1,060
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ah also need property_id comparisons on the joins, presumably.

    SELECT t3.* FROM tbl_availability AS t3
    INNER JOIN tbl_availability AS t1p ON t3.property_id = t1p.property_id AND t3.ddate > t1p.ddate
    LEFT JOIN tbl_availability AS i ON i.property_id = t3.property_id AND i.ddate > t1p.ddate AND i.ddate < t3.ddate WHERE t3.status = '3' and t1p.status = '1' AND i.ddate IS NULL AND t3.property_id = '45'

  8. #8
    SitePoint Wizard Ren's Avatar
    Join Date
    Aug 2003
    Location
    UK
    Posts
    1,060
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    SELECT t3.* FROM tbl_availability AS t3
    INNER JOIN tbl_availability AS t1p ON t3.property_id = t1p.property_id AND t3.ddate > t1p.ddate
    LEFT JOIN tbl_availability AS i ON i.property_id = t3.property_id AND i.ddate > t1p.ddate AND i.ddate < t3.ddate WHERE t3.status = '3' and t1p.status = '1' AND i.ddate IS NULL AND t3.property_id = '45'
    UNION SELECT t3.* FROM tbl_availability AS t3
    INNER JOIN tbl_availability AS t1f ON t3.property_id = t1f.property_id AND t3.ddate < t1f.ddate
    LEFT JOIN tbl_availability AS i ON i.property_id = t3.property_id AND i.ddate > t3.ddate AND i.ddate < t1f.ddate WHERE t3.status = '3' and t1f.status = '1' AND i.ddate IS NULL AND t3.property_id = '45'

    I think is the full solution.

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Ward
    I want to be able to select only the dates where status 3 is preceeded by 1 and followed by 1. In the case above that would return 2 results 2006-02-04 and 2006-02-07.
    actually, that's not true, it will return nothing

    however, if you change the requirement to:
    select only the dates where status 3 is preceeded by 1 or followed by 1
    then you will indeed get back those two rows

    here's the solution in version 4.1:
    Code:
    SELECT t3.* 
      FROM tbl_availability AS t3 
    inner
      join tbl_availability as t1p
        on t1p.property_id = t3.property_id
       and t1p.status = 1
       and t1p.ddate 
         = ( select max(ddate)
               from tbl_availability
              where property_id = t3.property_id
                and ddate < t3.ddate )
     where t3.status = 3                   
    union all
    SELECT t3.* 
      FROM tbl_availability AS t3 inner
      join tbl_availability as t1f
        on t1f.property_id = t3.property_id
       and t1f.status = 1
       and t1f.ddate 
         = ( select min(ddate)
               from tbl_availability
              where property_id = t3.property_id
                and ddate > t3.ddate )
     where t3.status = 3
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    SitePoint Enthusiast
    Join Date
    Oct 2001
    Location
    Helston, Cornwall, England
    Posts
    76
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ren and r937,

    Thanks very much for your help. I have access to MySQL 5 on my localhost so I can at least prepare for the day my host upgrades. In the meantime I think I'll be able to do a work around by inserting a different status code on the first and last entries where status is 3. This will then give me the option I require though not the perfect solution. Once again thanks.


Bookmarks

Posting Permissions

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