Mysql -是否有一种方法来查询多对多关系，并包括行不在链接表?

Question

我正在为一家拥有多个地点的公司设计一个库存管理应用程序。每个地点共享相同的基本产品集，但每个地点的库存产品数量因商店而异。

我已经创建了数据库的基本线框:

https://www.db-fiddle.com/f/49paZZocLknGr23Woabp7Q/1

以下是表格和一些示例数据:

CREATE TABLE locations (
id int primary key NOT NULL AUTO_INCREMENT,
name varchar(64)
);
CREATE TABLE products (
id int primary key NOT NULL AUTO_INCREMENT,
name varchar(64)
);
CREATE TABLE locations_products (
id int primary key NOT NULL AUTO_INCREMENT,
location_id int,
product_id int
);
INSERT INTO locations (name) VALUES
('phoenix'), ('denver'), ('houston'), ('dallas'), ('miami');

INSERT INTO products (name) VALUES
('nicotine'), 
('valium'), 
('vicodin'),
('marijuana'), 
('ecstasy'), 
('alcohol'), 
('cocaine'), 
('covid');

INSERT INTO locations_products (location_id, product_id) VALUES
(1, 1), (1, 2), (1, 3), (1, 4), (1, 5),
(2, 2), (2, 3), (2, 4), (2, 5),
(3, 1), (3, 2), (3, 3), (3, 4), (3, 5), (3, 6), (3, 7), (3, 8),
(4, 6), (4, 7), (4, 8),
(5, 1), (5, 2), (5, 3), (5, 4);

locations_products表存储位置和产品之间的多对多关系。如果产品在特定位置有库存，则location_id和product_id的一行进入locations_products。

查询某一地点的库存产品很简单:

SELECT l.name, t.name
FROM tests t
INNER JOIN locations_tests lt
ON t.id = lt.test_id
INNER JOIN locations l
ON lt.location_id = l.id
where lt.location_id = 3;

然而，获取没有库存的产品更困难，并且查询变得有些复杂，以获取所有产品的列表以及它们是否有库存:

SELECT t.name, l.name as location, 'yes' as in_stock
FROM  products t
LEFT JOIN locations_products lp
ON t.id = lp.product_id
LEFT JOIN locations l
ON lp.location_id = l.id
WHERE l.id = 1
UNION 
SELECT distinct p.name, 'phoenix' as location, 'no' as in_stock
from products p
LEFT JOIN locations_products lp
ON p.id = lp.product_id
LEFT JOIN locations l
ON lp.product_id = l.id
where p.id not in (
select lp.product_id from locations_products lp where lp.location_id = 1
)

该查询产生所需的结果，但是它有问题。将位置名称硬编码到SELECT语句中是非常难看的，并且DISTINCT查询会发出危险信号。

谁能建议一个更好的方法来获得相同的结果集在这个位置，但没有硬编码SELECT语句在UNION语句的第二个查询?我也愿意听取有关解决这个问题的更好方法的建议。谢谢!

<表类>名称位置in_stocktbody><<tr>尼古丁凤凰对安定凤凰对维柯丁凤凰对大麻凤凰对狂喜凤凰对酒精凤凰没有可卡因凤凰没有covid凤凰没有

使用IF()表达式测试是否找到链接列

要获得所有行中的位置名称，请添加与locations的交叉连接，该连接不依赖于该产品是否有库存。

SELECT t.name, l1.name as location, IF(l.id IS NULL, 'no', 'yes') as in_stock
FROM  products t
CROSS JOIN locations AS l1
LEFT JOIN locations_products lp
ON t.id = lp.product_id AND lp.location_id = l1.id
LEFT JOIN locations l
ON lp.location_id = l.id
WHERE l1.id = 1

还将连接表上的条件移动到ON子句中，这样它就不会从结果中过滤掉不匹配的行。

演示