SitePoint Sponsor

User Tag List

Results 1 to 3 of 3

Thread: Query problem

  1. #1
    SitePoint Zealot zbing's Avatar
    Join Date
    Jun 2002
    Location
    Lisbon, Portugal
    Posts
    159
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Unhappy Query problem! help needed!

    Hi there people!

    i have 2 tables that hold data for a event schedule

    table(months) stores the months of the year on these fields
    m_id | m_month | m_status
    1 | january | online
    2 | february | online
    etc...

    the m_status is either online or offline


    table(events) stores the events
    ev_id | ev_m_id | ev_day | ev_place | ev_status
    1 | 1 | 1 | here | online
    1 | 1 | 6 | there | online
    1 | 2 | 3 | here | online
    1 | 2 | 7 | here | online

    ev_m_id is the id of the month

    and what i need to do is: to get the last 3 scheduled events.

    and i did the following:

    SELECT m_id, m_status FROM months WHERE m_id<'13' AND m_status='online' ORDER BY m_id DESC LIMIT 2;

    this gets me the last 2 online months....

    the result of this query is stored in a PHP variable to be used in the next query.

    and

    SELECT ev_day FROM events WHERE ev_m_id='$m_id' AND ev_day<'32' ORDER BY ev_day DESC LIMIT 3;

    and this gets me the last 3 scheduled events on the months that i got before...

    but if there are eg. 3 events in january and 2 in february i get 5 events and i just want to get the last 3, eg. the 2 from february and the last one in january...

    hope anyone can help me with this

    thnx in advance
    Last edited by zbing; Jul 25, 2003 at 08:08.
    zbing

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    to get the last three scheduled events, sort them into newest-to-oldest sequence, which is month/day descending, and then use LIMIT to simply pick off the first three rows returned --
    Code:
    select ev_id
         , ev_m_id
         , ev_day
         , ev_place
         , ev_status
      from events
    order
        by ev_m_id  desc
         , ev_day   desc
    limit 3
    you will have some problems with this over the december/january border, so you will probably want to add a year column

    oh heck, why not bite the bullet and just replace year, month, and day columns with one DATE column



    rudy
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Zealot zbing's Avatar
    Join Date
    Jun 2002
    Location
    Lisbon, Portugal
    Posts
    159
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    thnx for yer reply m8! i solved it by adding a date column to the events table and can now get the relevant data out.

    thnx again for yer help!
    zbing


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
  •