MySQL MySQL内连接查询多表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14673338/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL Inner Join Query Multiple Tables
提问by Daniel
I am trying to join up a few tables and examples of the layouts are below:
我正在尝试连接一些表格，布局示例如下：
orders
订单
user_id=7 pricing id=37
products_pricing
产品_定价
id=37 product_id=33
products
产品
id=33 name=test product
SQL
SQL
SELECT *
FROM orders
INNER JOIN products_pricing
ON orders.pricing_id = products_pricing.id
INNER JOIN products
ON products_pricing.product_id = products.id
WHERE orders.user_id = '7' ");
listings
房源
id=233 user_id=7 url=test.com
With this SQL i get an output giving me all the products from the user_id of 7 and it will list each products name in a while loop. However when I add another INNER JOIN for a table called listings, which has a user_id column and I need to grab a url for each row that matches so I can hyperlink the product names with the url I get everything contained in the listings table as well as the working stuff above. I'm either doing it very wrong or am missing something. I've spent a few hours trying to figure it out but keep getting the same result. Can anyone help me out?
使用此 SQL，我得到一个输出，其中包含 user_id 为 7 的所有产品，它将在 while 循环中列出每个产品名称。但是，当我为一个名为列表的表添加另一个 INNER JOIN 时，该表有一个 user_id 列，我需要为匹配的每一行获取一个 url，以便我可以将产品名称与 url 超链接，我也得到列表表中包含的所有内容作为上面的工作内容。我要么做错了，要么错过了一些东西。我花了几个小时试图弄清楚，但一直得到相同的结果。谁能帮我吗？
回答by Mahmoud Gamal
Try this:
尝试这个：
SELECT
p.id,
p.name,
l.url,
o.user_id,
o.pricing_id
FROM orders AS o
INNER JOIN products_pricing AS pp ON o.pricing_id = pp.id
INNER JOIN products AS p ON pp.product_id = p.id
INNER JOIN listings AS l ON l.user_id = o.user_id
WHERE o.user_id ='7'
AND l.id = 233
AND l.url = 'test.com';
SQL Fiddle Demo
SQL 小提琴演示
For the sample data you posted in your question, this will give you:
对于您在问题中发布的示例数据，这将为您提供：
| ID | NAME | URL | USER_ID | PRICING_ID |
------------------------------------------------------
| 33 | testproduct | test.com | 7 | 37 |
回答by user2001117
Yes this can be done using the INNER join itself.and fetch select column in select statement.
是的，这可以使用内部连接本身来完成。并在选择语句中获取选择列。
SELECT
p.id,
p.name,
l.url,
o.user_id,
o.pricing_id
FROM orders AS o
INNER JOIN products_pricing AS pp ON o.pricing_id = pp.id
INNER JOIN products AS p ON pp.product_id = p.id
INNER JOIN listings AS l ON l.user_id = o.user_id
WHERE o.user_id ='7'
AND l.id = 233
AND l.url = 'test.com';
