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, '<p>this is dummy</p>', '<p>dummy</p>', '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
Best Answer
Try this =>