MySQL UNION query syntax

Question

I have two tables that I want to query and merge based on a category_id.

My events table has a column called event_category_id that creates a relationship between the event and it's category. The value is an int.

In my categories table I have a column called category_id which is what I want to match on and replace the int value with the varchar value of category_name.

What's my syntax? Thanks for the help!

categories CREATE TABLEwp_wild_dbem_categories( category_idint(11) NOT NULL auto_increment, category_nametinytext NOT NULL, PRIMARY KEY (category_id) ) ENGINE=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=latin1

events CREATE TABLEwp_wild_dbem_events( event_idmediumint(9) NOT NULL auto_increment, event_authormediumint(9) default NULL, event_nametinytext NOT NULL, event_start_timetime NOT NULL default '00:00:00', event_end_timetime NOT NULL default '00:00:00', event_start_datedate NOT NULL default '0000-00-00', event_end_datedate default NULL, event_notestext, event_rsvptinyint(1) NOT NULL default '0', event_seatstinyint(4) default NULL, event_contactperson_idmediumint(9) default NULL, location_idmediumint(9) NOT NULL default '0', recurrence_idmediumint(9) default NULL, event_category_idint(11) default NULL, UNIQUE KEYevent_id(event_id) ) ENGINE=MyISAM AUTO_INCREMENT=26 DEFAULT CHARSET=latin1

Answer 1

I don't think you want a UNION. You probably want something like this:

SELECT e.event_id, e.other_field, c.category_name
FROM events e JOIN categories c ON (e.category_id=c.category_id)
WHERE some_condition;

Answer 2

select * from events e join categories c on (c.category_id=e.event_category_id);

Answer 3

It's not UNION but JOIN if I understood correctly

SELECT c.category_name, e.* FROM events e JOIN categories c ON (c.category_id = e.event_category_id);