SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Member
    Join Date
    Sep 2005
    Posts
    16
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Need Help Combining Tables

    Hello All

    I need help combining two tables in my MySQL database. First, here are my two tables:

    Code:
    TABLE: events             TABLE: staticEvents
    id - int                  id - int
    eventName - text          eventName - text
    eventDate - date          eventMonth - tinyint
    eventLength - int         eventDay - tinyint
                              eventLength - int
    What I am looking for is an output similar to this, when I make a query for the current month/year:

    Code:
    +--------------+-----------------+----------------+
    |eventDate     |  eventName      |  eventLength   |
    +--------------+-----------------+----------------+
    | 2011-12-01  | event01          | 1              |
    | 2011-12-01  | event02          | 7              |    
    | 2011-12-15  | staticEvent01    | 1              |    
    | 2011-12-25  | event02          | 5              |    
    | 2011-12-31  | staticEvent02    | 1              |    
    +--------------+-----------------+----------------+
    I intentionally made the "date" in staticEvents the way I did for a reason, so I can't really change it. If anybody could help a guy out with a query, I would really appreciate it.

    Thanks for taking the time to read, and have a great day.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,015
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Code:
    SELECT eventName
         , eventDate
         , eventLength
      FROM events
    UNION ALL
    SELECT eventName
         , CAST(CONCAT_WS('-','2011',eventMonth,eventDay) AS DATE)
         , eventLength
      FROM staticEvents
    ORDER 
        BY eventDate
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Member
    Join Date
    Sep 2005
    Posts
    16
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    HEY ... that is great, thank you! I made a small change to look for events for the current month/year. What do you think?

    Code:
    SELECT eventName,eventDate,eventLength
    FROM events where eventDate like '2011-12%'
    UNION ALL SELECT eventName,
    CAST(CONCAT_WS('-','2011',eventMonth,eventDay) AS DATE), eventLength
    FROM staticEvents where eventMonth = '12'
    ORDER BY eventDate, eventName"

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,015
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    change this --
    Code:
    where eventDate like '2011-12%'
    to this --
    Code:
    where eventDate >= '2011-12-01'
      and eventDate  < '2012-01-01'
    mysql is a very forgiving database, but relying on implicit conversions is not a winning strategy in the long run

    it's better to code explicitly with the correct datatype in mind, and the range test is best practice for dates because it also allows for the query to be optimized (i.e. use an index)

    for the other table, change this --
    Code:
    where eventMonth = '12'
    to this --
    Code:
    where eventMonth = 12
    once again, eschew the implicit conversion
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Member
    Join Date
    Sep 2005
    Posts
    16
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    It's done, and thanks again for the query!

    Have a great day.

  6. #6
    SitePoint Enthusiast cmsfan's Avatar
    Join Date
    Dec 2011
    Location
    holland
    Posts
    31
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thank you for sking that just made me learn a lot to.
    That what i love about forums, you dont help one but you help many as other will read it to.


Tags for this Thread

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
  •