Ayuda con Select con Join

[arts]

CREATE DATABASE IF NOT EXISTS comercio;
USE comercio;

DROP TABLE IF EXISTS `ecomm_customers`;
CREATE TABLE `ecomm_customers` (
`customer_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`first_name` varchar(20) NOT NULL,
`last_name` varchar(20) NOT NULL,
`address_1` varchar(50) NOT NULL,
`address_2` varchar(50) DEFAULT NULL,
`city` varchar(20) NOT NULL,
`state` char(2) NOT NULL,
`zip_code` char(5) NOT NULL,
`phone` char(12) NOT NULL,
`email` varchar(100) NOT NULL,
PRIMARY KEY (`customer_id`)
) ENGINE=MyISAM AUTO_INCREMENT=6 DEFAULT CHARSET=latin1;


DROP TABLE IF EXISTS `ecomm_order_details`;
CREATE TABLE `ecomm_order_details` (
`order_id` int(10) unsigned NOT NULL,
`order_qty` int(10) unsigned NOT NULL,
`product_code` char(5) NOT NULL,
KEY `order_id` (`order_id`),
KEY `product_code` (`product_code`)
foreign key (order_id) references ecomm_orders (order_id),
foreign key (product_code) references productos_sin_dvd (codigo)

) ENGINE=MyISAM DEFAULT CHARSET=latin1;


DROP TABLE IF EXISTS `ecomm_orders`;
CREATE TABLE `ecomm_orders` (
`order_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`order_date` date NOT NULL,
`customer_id` int(10) unsigned NOT NULL,
`cost_subtotal` decimal(7,2) NOT NULL,
`cost_shipping` decimal(6,2) DEFAULT NULL,
`cost_tax` decimal(6,2) DEFAULT NULL,
`cost_total` decimal(7,2) NOT NULL,
`shipping_first_name` varchar(20) NOT NULL,
`shipping_last_name` varchar(20) NOT NULL,
`shipping_address_1` varchar(50) NOT NULL,
`shipping_address_2` varchar(50) DEFAULT NULL,
`shipping_city` varchar(20) NOT NULL,
`shipping_state` char(2) NOT NULL,
`shipping_zip_code` char(5) NOT NULL,
`shipping_phone` char(12) NOT NULL,
`shipping_email` varchar(100) NOT NULL,
PRIMARY KEY (`order_id`),
KEY `customer_id` (`customer_id`)
foreign key (customer_id) references ecomm_customers(customer_id)
) ENGINE=MyISAM AUTO_INCREMENT=12 DEFAULT CHARSET=latin1;

--
-- Dumping data for table `ecomm_orders`
--

DROP TABLE IF EXISTS `ecomm_temp_cart`;
CREATE TABLE `ecomm_temp_cart` (
`session` char(50) NOT NULL,
`product_code` char(5) NOT NULL,
`qty` int(10) unsigned NOT NULL,
PRIMARY KEY (`session`,`product_code`),
KEY `product_code` (`product_code`)
foreign key (product_code) references productos_sin_dvd(product_code),
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

Productossin_dvd, novedades y dvd tienen los mismos campos pero son tablas independientes.

CREATE TABLE `productos_sin_dvd` (
`codigo` char(5) NOT NULL,
`nombre` varchar(100) NOT NULL,
`descripcion_castellano` mediumtext,
`precio` decimal(6,2) NOT NULL,
PRIMARY KEY (`codigo`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

El problema creo que esta en las tablas productos_sin_dvd, dvd y novedades que las añadi despues de haber teniedo simplemente productos_sin_dvd pero en cuanto meti en tablas diferentes novedades y dvd la cosa se empezo a complicar, tanto que incluso para mostrar productos ya eran 3 tablas y tenia que ir haciendo select tal from taltabla union distinct (select tal from novedades) union distinct sleect tal from dvd haciendo todo mas complicado.