][one] catalog_mods table

catalog catalog table

You need to pull the data on the product from two tables.

From the catalog table, pull cat_id , cat_name with the condition - where cat_special = 1 .

From the second table - catalog_mods pull mod_price , mod_id , mod_parent .

You need to catalog.cat_id = catalog_mods.mod_parent tables through catalog.cat_id = catalog_mods.mod_parent .

The problem is that in the catalog_mods table there are several rows with the same mod_parent for each product, but you need to somehow pull out one row for each mod_parent , where mod_price has the minimum value. Help please make a request. There somehow probably through INNER JOIN and MIN(mod_price) . I tried to do this myself

 $connection->query('SELECT DISTINCT cat_id, cat_name, cat_special FROM `catalog` INNER JOIN (SELECT mod_id, MIN(mod_price) as min_mod_price, mod_parent FROM catalog_mods GROUP BY mod_price) tbl_catalog_mods ON catalog.cat_id = tbl_catalog_mods.mod_parent WHERE catalog.cat_special = 1');

received several arrays of this content:

 array(3) { ["cat_id"]=> string(3) "158" ["cat_name"]=> string(8) "Spread-5" ["cat_special"]=> string(1) "1" }

that is, what pulled out of catalog_mods as a result did not get out

  • please create (for example) sqlfiddle.com a minimal example of your data. then it will be easier to give you a suitable request. - aleksandr barakin
  • here is the link to the database fayloobmennik.net/5512398 . I could not create a database in this service you have - sew810i9

1 answer 1

if I correctly understood the required, then you can make such, for example, request:

SQL feeddle

MySQL 5.6 Schema Setup :

 -- phpMyAdmin SQL Dump -- version 4.0.10.10 -- http://www.phpmyadmin.net -- -- Хост: 127.0.0.1:3306 -- Время создания: Ноя 03 2015 г., 18:41 -- Версия сервера: 5.5.45 -- Версия PHP: 5.5.28 SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO"; SET time_zone = "+00:00"; /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; /*!40101 SET NAMES utf8 */; -- -- База данных: `test` -- -- -------------------------------------------------------- -- -- Структура таблицы `catalog` -- CREATE TABLE IF NOT EXISTS `catalog` ( `cat_id` int(9) NOT NULL AUTO_INCREMENT, `cat_name` varchar(200) NOT NULL, `cat_special` int(9) NOT NULL, PRIMARY KEY (`cat_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=11 ; -- -- Дамп данных таблицы `catalog` -- INSERT INTO `catalog` (`cat_id`, `cat_name`, `cat_special`) VALUES (1, 'name 1', 1), (2, 'name 2', 1), (3, 'name 3', 0), (4, 'name 4', 1), (5, 'name 5', 1), (6, 'name 6', 0), (7, 'name 7', 1), (8, 'name 8', 0), (9, 'name 9', 1), (10, 'name 10', 1); -- -------------------------------------------------------- -- -- Структура таблицы `catalog_mods` -- CREATE TABLE IF NOT EXISTS `catalog_mods` ( `mod_id` int(9) NOT NULL AUTO_INCREMENT, `mod_parent` int(9) NOT NULL, `mod_price` int(11) NOT NULL, PRIMARY KEY (`mod_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=13 ; -- -- Дамп данных таблицы `catalog_mods` -- INSERT INTO `catalog_mods` (`mod_id`, `mod_parent`, `mod_price`) VALUES (1, 1, 3434), (2, 1, 1236), (3, 1, 4567), (4, 1, 7586), (5, 2, 4376), (6, 2, 3276), (7, 2, 6482), (8, 2, 4365), (9, 3, 2315), (10, 3, 3475), (11, 3, 4597), (12, 3, 5689); /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */; /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */; /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

Query 1 :

 select catalog.*, min(catalog_mods.mod_price) from catalog left join catalog_mods on catalog.cat_id = catalog_mods.mod_parent group by cat_id

Results :

 | cat_id | cat_name | cat_special | min(catalog_mods.mod_price) | |--------|----------|-------------|-----------------------------| | 1 | name 1 | 1 | 1236 | | 2 | name 2 | 1 | 3276 | | 3 | name 3 | 0 | 2315 | | 4 | name 4 | 1 | (null) | | 5 | name 5 | 1 | (null) | | 6 | name 6 | 0 | (null) | | 7 | name 7 | 1 | (null) | | 8 | name 8 | 0 | (null) | | 9 | name 9 | 1 | (null) | | 10 | name 10 | 1 | (null) |
  • Yes, what you need! Thank you so much. I read articles about merging queries, but as I begin to write such queries, it turns out nonsense. Can you tell me a good article on this topic? - sew810i9
  • if you are given an exhaustive answer, please tick it accepted ("tick" to the left of the answer). - aleksandr barakin