Need Help Combining Tables

Databases
1

Hello All

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


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:


+--------------+-----------------+----------------+
|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. :slight_smile:

2 
SELECT eventName
     , eventDate
     , eventLength
  FROM events
UNION ALL
SELECT eventName
     , CAST(CONCAT_WS('-','2011',eventMonth,eventDay) AS DATE)
     , eventLength
  FROM staticEvents
ORDER 
    BY eventDate
3

HEY … that is great, thank you! I made a small change to look for events for the current month/year. What do you think?


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

change this –

where eventDate like '2011-12%'

to this –

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 –

where eventMonth = '12'

to this –

where eventMonth = 12

once again, eschew the implicit conversion

5

It’s done, and thanks again for the query!

Have a great day. :slight_smile:

6

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.