MYSQL从多个表中提取多个值

Forgive me my title is poorly chosen, I'm not sure what to label this question but believe I can explain it.

请原谅我的题目选得不好，我不知道该给这个问题贴上什么标签，但我相信我可以解释它。

I have a many to many relationship between a products table and a stores table.

我在product表和store表之间有很多很多的关系。

table name: products
+-------+------------------+------+-----+---------+----------------+
| Field | Type             | Null | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+
| id    | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| name  | varchar(200)     | NO   |     | NULL    |                |
| sku   | varchar(10)      | NO   |     | NULL    |                |
| units | int(5)           | YES  |     | NULL    |                |
+-------+------------------+------+-----+---------+----------------+

table name: stores
+----------+------------------+------+-----+---------+----------------+
| Field    | Type             | Null | Key | Default | Extra          |
+----------+------------------+------+-----+---------+----------------+
| id       | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| number   | int(5)           | NO   |     | NULL    |                |
| location | varchar(50)      | NO   |     | NULL    |                |
| phone    | varchar(10)      | YES  |     | NULL    |                |
+----------+------------------+------+-----+---------+----------------+


table name: products_stores
+-------+------------------+------+-----+---------+-------+
| Field | Type             | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+-------+
| pID   | int(10) unsigned | NO   | PRI | NULL    |       |
| sID   | int(10) unsigned | NO   | PRI | NULL    |       |
+-------+------------------+------+-----+---------+-------+

I am running a query to display all products available at the store where its ID = 1

我正在运行一个查询，以显示在其ID = 1的商店中可用的所有产品。

SELECT p.name, p.sku 
FROM products p 
  INNER JOIN products_stores ps
    ON p.id = ps.pID 
WHERE ps.sID = 1;

I have no problem with this query and get the results I desire. However, in my display results I would also like to show the store location value for each result row. I thought that I would be able to just include it comma separated in my FROM statement such as:

我对这个查询没有问题，并得到我想要的结果。然而，在我的显示结果中，我还想显示每个结果行的存储位置值。我想我可以将它包含在我的FROM语句中，如:

SELECT p.name, p.sku, s.location
FROM products p, stores s
  INNER JOIN products_stores ps
    ON p.id = ps.pID
WHERE ps.sID = 1;

However this does not work. I'm sure the answer is very simple and I will continue to research for a solution. I figured I would throw this up in hopes that somebody might be able lead me in the right direction.

然而，这行不通。我相信答案很简单，我将继续研究解决方案。我想我会把这个扔出去，希望有人能把我引向正确的方向。

Thanks.

谢谢。

2 个解决方案

#1

Use explicit JOINs,implict JOIN are deprecated(and less clear)

使用显式连接，不赞成使用implict连接(不明确)

SELECT p.name, p.sku ,s.location
FROM products p 
  INNER JOIN products_stores ps
    ON p.id = ps.pID 
INNER JOIN stores s
ON s.ID=ps.sID
WHERE ps.sID = 1;

#2

SELECT 
  p.name,
  p.sku,
  s.location 
FROM
  products p,
  stores s,
  products_stores ps 
WHERE p.id = ps.pID 
  AND s.sID = ps.sID 
  AND ps.sID = 1 ;