How do I merge data from two tables with same field names?

I've got two tables that are virtually identical (I know I should have laid things out differently). I am trying to query both tables but I want to merge the results so that the results are displayed as if they came from one table.

Here's what the look like:

CREATE TABLE `mycoupons` (
`id` bigint(20) NOT NULL auto_increment,
`upc` bigint(20) NOT NULL,
`description` varchar(255) NOT NULL,
`expirdate` date NOT NULL,
`onhand` int(11) NOT NULL default '1',
`location` varchar(255) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=159 ;

and

CREATE TABLE `mytrades` (
`id` bigint(20) NOT NULL auto_increment,
`upc` bigint(20) NOT NULL,
`description` varchar(255) NOT NULL,
`expirdate` date NOT NULL,
`onhand` int(11) NOT NULL default '1',
`location` varchar(255) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=176 ;


I want to pull description, expirdate, onhand, and location from both tables and sort by expirdate . I can't figure out how to merge the results for each field so right now I'm getting two descriptions, two expirdates, etc.

Can someone tell me how to format my query?

Many thanks!

 

If the tables are unrelated and you just want to show all data from both tables, but in an ordered format, you can use the UNION statement - eg:

SELECT description, expirdate, onhand, location FROM mycoupons
UNION
SELECT description, expirdate, onhand, location FROM mytrades
ORDER BY expirdate;

This would merge the two selects together. If the tables are joined somehow (via ID) then the solution will be different but if you just want to show all rows from both tables in one result set, then something like the above should do it.

 

That's exactly what I needed! Thanks wootty!