0

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

0

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)
0

Okay there, Am not sure why you are using distinct but from my understanding, i think you want to select an id_order_details, a corresponding id_product and id_image where cover = 1.

SELECT a.id_order_detail, b.id_product, c.id_image FROM ps_order_detail AS a JOIN ps_product_lang AS b ON b.id_product = a.product_id JOIN ps_image AS c ON (c.id_product = a.product_id AND c.cover = 1);

To restrict your query to a particular order, you can then introduce the where clause.

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.