Get the value of one attribute from the reference key of another table? I have two tables named as ps_order_detail and ps_product_lang in MySQL. I want to get the product name from one table whose reference key is in another table. I have two tables. First the ps_order_detail database table is like this

CREATE TABLE IF NOT EXISTS `ps_order_detail` (
  `id_order_detail` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `product_id` int(10) unsigned NOT NULL,
  `product_attribute_id` int(10) unsigned DEFAULT NULL,
  `product_name` varchar(255) NOT NULL,
  PRIMARY KEY (`id_order_detail`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=3 ;

--
-- Dumping data for table `ps_order_detail`
--

INSERT INTO `ps_order_detail` (`id_order_detail`, `product_id`, `product_attribute_id`, `product_name`) VALUES
(1, 5, 11, 'iPod touch - Capacité: 32Go'),
(2, 2, 2, 'iPod shuffle - Color : Green'),
(3, 5, 9, 'iPod touch - Disk space : 8GB');


and another table for ps_product_lang is like this

CREATE TABLE IF NOT EXISTS `ps_product_lang` (
  `id_product` int(10) unsigned NOT NULL,
  `id_shop` int(11) unsigned NOT NULL DEFAULT '1',
  `id_lang` int(10) unsigned NOT NULL,
  `name` varchar(128) NOT NULL,
  PRIMARY KEY (`id_product`,`id_shop`,`id_lang`),
  KEY `id_lang` (`id_lang`),
  KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Dumping data for table `ps_product_lang`
--

INSERT INTO `ps_product_lang` (`id_product`, `id_shop`, `id_lang`,  `name`) VALUES
(1, 1, 1, 'iPod Nano', 'In stock'),
(2, 1, 1,  'iPod shuffle'),
(3, 1, 1,  'MacBook Air'),
(4, 1, 1, 'MacBook'),
(5, 1, 1, 'iPod touch'),
(6, 1, 1, 'Belkin Leather Folio for iPod nano - Black / Chocolate'),
(7, 1, 1, 'Shure SE210 Sound-Isolating Earphones for iPod and iPhone'),
(8, 1, 1, 'Lappy');


Now you can see that in the table  ps_order_detailthere is one column named as product_id and in the table ps_product_lang there is one column for name . so my question is how can I get the name of product by combining both ps_product_lang and ps_order_detail table. 
I want to get the product name from ps_order_detail product_ida attribute? So any help and suggestions will be really appreciable. Thanks.
Try this:

SELECT DISTINCT a.product_name
FROM `ps_order_detail` a 
JOIN ps_product_lang b 
    ON a.product_id=b.id_product