Posts Tagged with joins

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

Есть таблица товаров.

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

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

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

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

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

Даны следующие две таблицы:

--

-- Структура таблицы `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

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