加入4个表mySQL

Question

我有4个表产品，产品_地点，商店，地点

Products = [Product_ID, P_Name, Price]
Products_Location = [Location_ID,Product_ID]
Locations = [Location_ID,Stores_ID, L_Name]
Stores = [Stores_ID, S_Name] 

我试图在一个表中显示每个表中的列，但不起作用我试过两次使用内部联接，但没有成功(如果我使用1个内部联接，它就会成功(这是我的代码

"SELECT Products.P_Name, Products.Price, Stores.S_Name, Locations.L_Name, Products.Product_ID 
From Products, Locations
INNER JOIN Stores ON Locations.Stores_ID = Stores.Stores_ID 
INNER JOIN Products_Location ON Products.Product_ID = Products_Location.Product_ID
Where P_Name LIKE '%$search%' OR Price LIKE '%$search%' OR S_Name LIKE '%$search%' OR L_Name LIKE '%$search%'
ORDER BY Price ASC";

Answer 1

由于您似乎对SQL还比较陌生，所以让我帮您一点忙，而不是在新的帖子中受到抨击。使用join(left、right、full、outer等(编写查询通常都是基于TableA——一些到TableB的联接。所以，试着在脑海中想象你的桌子。他们之间的关系是什么。这就是加入。如果一个表连接到多个表，那么将其视为自己的JOIN。我通常尝试缩进显示TableA与TableB(或TableC、D、E等(连接的级别。

然后，你也可以习惯使用"；别名"；名称，这样您就不必一直写LONG表名称引用了。按照你最初的查询，它会更像。。。

select
p.p_name,
p.price,
s.s_name,
l.l_name,
p.product_id
From 
Products p
-- first get the JOIN from product to its locations where available
JOIN Products_Location pl
on p.product_id = pl.product_id
-- now, from the product location to the location table.
-- notice my indentation to show its under the product_location
-- and not directly from the product table.  Visually seeing the
-- hierarchical chain can help writing queries.
JOIN Locations l
on pl.location_id = l.location_id
-- and now, indent once more from location to the stores
JOIN Stores s
on l.stores_id = s.stores_id
where
-- NOW, you can put in your filtering criteria.  But if ever a left or right
-- based join, you would just add that part of the criteria directly within 
-- the JOIN portion
p.p_name like '%$search%' 
OR  p.Price LIKE '%$search%' 
OR  s.S_Name LIKE '%$search%' 
OR  l.L_Name LIKE '%$search%'
ORDER BY 
p.Price ASC

此外，请始终尝试使用table.column或alias.column限定查询中的所有列，以便其他人知道这些列来自哪个表，以防止调用中出现任何歧义。

Answer 2

您正在对产品和地点执行交叉联接。您的目标不是通过Products.Product_ID=Products_Location.Product_ID和Products_Location_Location_ID=Locations.Location_ID上的Products_Location表加入Products和Locations吗？