MySQL get values from the other table by using inner joins In mysql I have 3 tables called as

ps_product_lang

ps_order_detail

and ps_image


here are the tables sample data

=== ps_product_lang ===


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,
  `description` text,
  `description_short` text,
  `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`, `description`, `description_short`,  `name`) VALUES
(1, 1, 1, '&lt;p&gt;this is dummy&lt;/p&gt;', '&lt;p&gt;dummy&lt;/p&gt;', 'dummy product')
);


===== ps_order_detail===
    CREATE TABLE IF NOT EXISTS `ps_order_detail` (
      `id_order_detail` int(10) unsigned NOT NULL AUTO_INCREMENT,
      `id_order` int(10) unsigned NOT NULL,
      `product_id` int(10) unsigned NOT NULL,
      PRIMARY KEY (`id_order_detail`),
      KEY `product_id` (`product_id`)
    ) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=16 ;

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

    INSERT INTO `ps_order_detail` (`id_order_detail`, `id_order`,  `product_id`) VALUES
    (1, 1, 2);

========= ps_image =========
CREATE TABLE IF NOT EXISTS `ps_image` (
  `id_image` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `id_product` int(10) unsigned NOT NULL,
  `position` smallint(2) unsigned NOT NULL DEFAULT '0',
  `cover` tinyint(1) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`id_image`),
  UNIQUE KEY `idx_product_image` (`id_image`,`id_product`,`cover`),
  KEY `image_product` (`id_product`),
  KEY `id_product_cover` (`id_product`,`cover`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=24 ;

--
-- Dumping data for table `ps_image`
--

INSERT INTO `ps_image` (`id_image`, `id_product`, `position`, `cover`) VALUES
(1, 1, 1, 1),
(2, 1, 2, 0),
(3, 1, 3, 0),
(4, 1, 4, 0),
(5, 2, 1, 0),
(6, 2, 2, 0),
(7, 2, 3, 1),
(8, 3, 1, 1),
(9, 3, 2, 0),
(10, 4, 1, 1),
(11, 4, 2, 0),
(12, 5, 1, 1),
(13, 5, 2, 0),
(14, 5, 3, 0),
(15, 5, 4, 0),
(16, 6, 1, 1),
(17, 6, 2, 0),
(18, 6, 3, 0),
(19, 6, 4, 0),
(20, 7, 1, 1),
(21, 7, 2, 0),
(22, 7, 3, 0),
(23, 7, 4, 0);


So here I want to get all the field like 

id_order_detail id_product name id_image(from ps_image table where cover = 1)


To get id_order_detail id_product name I am using this mysql inner join

SELECT DISTINCT (a.id_order_detail),(b.id_product),(b.name)
        FROM ps_order_detail a join 
        ps_product_lang b on a.product_id=b.id_product GROUP BY(name)


This one is giving me id_order_detail id_product name

Now I want to get id_image from the ps_image table where the cover value is 1 for the product_id

So can someone tell me how to do this one? Any help and suggestions will be really appreciable. Thanks
Try this =>

SELECT DISTINCT (a.id_order_detail),(b.id_product),(b.name), img.id_image
        FROM ps_order_detail a join 
        ps_product_lang b on a.product_id=b.id_product 
        join ps_image img on img.id_product = b.id_product
where img.cover = 1
GROUP BY(name)