0

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.

1

Try this:

SELECT DISTINCT a.product_name
FROM `ps_order_detail` a 
JOIN ps_product_lang b 
    ON a.product_id=b.id_product
  • how to get the distinct name values from it? – newuser Feb 11 '14 at 5:41
  • updated the answer. – Vikas Umrao Feb 11 '14 at 5:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.