Как составить одну таблицу из нескольких SELECT запросов?

Question

[mk_qwerty]

Есть три таблицы:

CREATE TABLE store
(
	ID int not null auto_increment,
    CITY varchar(500) not null,
    
    PRIMARY KEY (ID)
);

CREATE TABLE book
(
    ID int not null auto_increment,
    NAME varchar(500) not null,
    ISSUE_YEAR YEAR,
    PUBLISHER_ID int,
    ISBN char(17),
 );

CREATE TABLE book_store
(
	BOOK_ID int not null,
    STORE_ID int not null,
    PRICE DECIMAL(10, 2),
    QUANTITY int unsigned not null default 0,
    
    PRIMARY KEY (BOOK_ID, STORE_ID),
    INDEX IX_BOOK (BOOK_ID),
    FOREIGN KEY FK_BOOK_STORE_BOOK (BOOK_ID) references book(ID)
        ON UPDATE RESTRICT
        ON DELETE RESTRICT,
    FOREIGN KEY FK_BOOK_STORE_BOOK (STORE_ID) references store(ID)
        ON UPDATE RESTRICT
        ON DELETE RESTRICT
);

Как составить такой запрос, чтобы столбцы ответа были такими: book.NAME (название книги), QUANTITY в City1 (количество на складе в City1 ), QUANTITY в City2 (количество на складе в в City2 ), разница между количеством книг в первом городе и втором?

Пробовал делать через UNION и Функции, но не получается составить всё в единую таблицу.

Answer 1

[Slava Rozhnev]

Это довольно просто:

SELECT
	book.NAME, 
	SUM(IF(store.CITY = 'Moscow', QUANTITY, 0)) AS CITY1_QUANTITY,
	SUM(IF(store.CITY = 'London', QUANTITY, 0)) AS CITY2_QUANTITY,
	SUM(IF(store.CITY = 'Moscow', QUANTITY, 0)- IF(store.CITY = 'London', QUANTITY, 0)) AS DIFF_QUANTITY
FROM book
LEFT JOIN book_store ON book_store.BOOK_ID = book.ID
JOIN store ON store.ID = book_store.STORE_ID
WHERE store.CITY IN ('Moscow', 'London')
GROUP BY book.NAME;

MySQL fiddle online

Comments

[mk_qwerty]

Спасибо!

Answer 2

[mentin]

1) Посчитать число книг в городе City1 и в City2 повторив дважды запрос
* Отфильтровать store по имени города.
* Join получившийся результат с book_store используя store_id.
* Group by book_id чтобы получить число книг в городе.

2) Сделать full outer join между двумя получившимися подзапросами используя book_id.

3) Осталось заменить NULL (если книги нет в городе) на 0, посчитать разницу, и сделать ещё один join с book чтобы принести имя книги.