Posts Tagged with joins

Displaying 1-8 of 8 results.
Not Resolved: MySQL count where columns fields have same value
posted by admin on November 1, 2016
I have rows, and I want count my select with criteria IN and want to count rows, which is having same values for all rows
Example:

id name lastname age
1 Abc defghij 12
2 Def asdasd 15
3 Ghi qwewqeq 18
4 Abc zzzzz 12
5 Abc yyy 12
6 Abc uuuu 12

IN array

1, 4, 5, 6

Same fields:

`name`, `age`

I want to see RESULTS LIKE
Result SELECT:

id name lastname age
1 Abc defghij 12
4 Abc zzzzz 12
5 Abc yyy 12

Result SELECT COUNT(*):

3
Read more
Resolved: Уникальные пары MySQL из одной таблицы. Distinct pairs in MySQL join (same table)
posted by admin on March 21, 2016
Есть таблица товаров.

CREATE TABLE `goods` ( `id` int(11) unsigned NOT NULL AUTO_INCREMENT, `name` varchar(64) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8


Она содержит следующие значения.

`id` `name` 1 Яблоки 2 Яблоки 3 Груши 4 Яблоки 5 Апельсины 6 Груши


Напишите запрос, выбирающий уникальные пары `id` товаров с одинаковыми `name`, например:

(1,2), (4,1), (2,4), (6,3)
Read more
Wiki: Joining Three or More Tables SYNTAXwiki
posted by admin on June 18, 2016
Although each join specification joins only two tables, FROM clauses can contain multiple join specifications. This allows many tables to be joined for a single query.

The ProductVendor table of the AdventureWorks2008R2 database offers a good example of a situation in which joining more than two tables is helpful. The following Transact-SQL query finds the names of all products of a particular subcategory and the names of their vendors:

SELECT p.Name, v.Name
FROM Production.Product p
JOIN Purchasing.ProductVendor pv
ON p.ProductID = pv.ProductID
JOIN Purchasing.Vendor v
ON pv.BusinessEntityID = v.BusinessEntityID
WHERE ProductSubcategoryID = 15
ORDER BY v.Name;
Read more
Resolved: How to get the latest record in each group using GROUP BY?
posted by admin on May 21, 2016
Let's say I have a table called messages with the columns:

id | from_id | to_id | subject | message | timestamp


I want to get the latest message from each user only, like you would see in your FaceBook inbox before you drill down into the actual thread.

This query seems to get me close to the result I need:

SELECT * FROM messages GROUP BY from_id


However the query is giving me the oldest message from each user and not the newest.

I can't figure this one out.Read more
Resolved: MySQL get most popular tags ids combination with their counts
posted by admin on April 18, 2016
I have three tables
1.tbl_tag

id int NOT NULL AI PK
name VARCHAR(50) NOT NULL

id name
1 lifestyle
2 store
3 car
4 luxury
5 classy
6 boy
7 girl
8 man
9 woman
10 strong
etc ...


2.tbl_media

id int NOT NULL AI PK
name VARCHAR(50) NOT NULL

id name
1 one
2 two
3 three
4 four
5 five
6 six
7 seven
8 eight
9 nine
10 ten
etc...


3. tbl_media_tag

id int NOT NULL AI PK
media_id int NOT NULL -> relationship with tbl_media
tag_id int NOT NULL -> relationship with tbl_tag

id media_id tag_id
1 1 4
2 1 3
3 4 3
4 5 2
5 6 8
6 4 4
7 8 7
8 7 4
9 7 3
10 6 2
11 8 4
12 8 3
etc...


I want to get most popular tags(tag_id) combinations with LIMIT, for example most popular top ten tags combination

I want to get result like this

4, 7, 3 (75)
4, 8 (42)
1, 7, 9, 10 (28)
7, 8, 9 (10)
7, 2 (2)
Read more
Resolved: MySQL get most popular categories with posts counts (category and posts tables)
posted by admin on March 16, 2016
I have categories table btbl_category

CREATE TABLE btbl_category(
id int auto_increment primary key,
name varchar(20)
);

INSERT INTO btbl_category (name)
VALUES
('one'),('two'),('three'),('four');

INSERT INTO apps (app_category)
VALUES
(2),(4),(2),(1);


I have categories table btbl_post

CREATE TABLE btbl_post
(
id int auto_increment primary key,
category_id int,
);

INSERT INTO btbl_post (category_id)
VALUES
(2),(4),(2),(1);


I want to select most popular categories with countsRead more
Resolved: MySQL задание менеджер, продукт, заявки, продажы. Задача из собеседования - PHP разработчик
posted by admin on March 16, 2016
Даны следующие две таблицы:

--
-- Структура таблицы `claim`
--
CREATE TABLE IF NOT EXISTS `claim` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`manager_id` int(11) NOT NULL,
`created_at` datetime NOT NULL,
`sum` float NOT NULL,
PRIMARY KEY (`id`),
KEY `manager_id` (`manager_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=9 ;
--
-- Дамп данных таблицы `claim`
--

INSERT INTO `claim` (`id`, `manager_id`, `created_at`, `sum`) VALUES
(1, 1, '2013-07-18 10:23:08', 256.128),
(2, 3, '2013-07-18 13:29:49', 512.1),
(3, 4, '2013-07-19 16:29:07', 123),
(4, 4, '2013-07-18 17:35:53', 321),
(5, 4, '2013-06-19 15:31:46', 756),
(6, 1, '2013-06-05 10:47:26', 265),
(7, 2, '2013-05-31 20:27:38', 354),
(8, 4, '2013-07-17 15:48:20', 798.12);
----------------------------------------------------------

--
-- Структура таблицы `manager`
--
CREATE TABLE IF NOT EXISTS `manager` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`first_name` varchar(32) CHARACTER SET latin1 NOT NULL,
`last_name` varchar(32) CHARACTER SET latin1 NOT NULL,
`email` varchar(32) CHARACTER SET latin1 NOT NULL,
`chief_id` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `chief_id` (`chief_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=5 ;
--
-- Дамп данных таблицы `manager`
--
INSERT INTO `manager` (`id`, `first_name`, `last_name`, `email`, `chief_id`) VALUES
(1, 'Simple', 'Manager', '', 2),
(2, 'Super', 'Manager', '', NULL),
(3, 'Third ', 'Manager', '', 2),
(4, 'Just', 'Manager', '', NULL);

--
-- Ограничения внешнего ключа таблицы `claim`
--
ALTER TABLE `claim`
ADD CONSTRAINT `claim_ibfk_1` FOREIGN KEY (`manager_id`) REFERENCES `manager` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Ограничения внешнего ключа таблицы `manager`
--
ALTER TABLE `manager`
ADD CONSTRAINT `manager_ibfk_1` FOREIGN KEY (`chief_id`) REFERENCES `manager` (`id`) ON DELETE SET NULL ON UPDATE CASCADE;


Задача 1

Напишите sql-запрос, выбирающий информацию по каждому менеджеру, включая количество связанных с ним заявок и их общую сумму (в 2 дополнительных поля: claim_count, claim_total_sum).


Задача 2

Напишите запрос, который выведет двух менеджеров, у которых количество связанных заявок меньше, чем у остальных. При этом, объедините значения first_name и last_name в одно поле full_name.
Read more
Resolved: Three table MySQL relation task, city, product, sale max join query
posted by admin on March 11, 2016
We have three tables, city, product, sale

city
  • id PK AI INT(10)
  • name VARCHAR(50)


product
  • id PK AI INT(10)
  • name VARCHAR(50)


sale
  • id PK AI INT(10)
  • city_id INT(10)
  • product_id INT(10)
  • amount INT(10)
  • total_price INT(10)


Task, Задача

Написать SQL-запрос, который выведет результат вида: город, товар, количество проданного товара, итоговая сумма. Т.е. для каждого города и для каждого товара нужно вывести сколько было продано данного товара и на какую сумму. Если в каком-то городе какой-то товар не был продан вовсе — выводить нули в полях "количество" и "сумма".
Read more