在 MySQL 中一次查询两个表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4960295/
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
Querying two tables at once in MySQL
提问by Harri
I have two tables; productstorymapand stories. productstorymaphas productIdand storyIdcolumns. storieshas idand statuscolumns.
我有两张桌子；productstorymap和stories。productstorymap有productId和storyId列。stories有id和status列。
I need to query all stories which belong to a certain product and have certain status. Closest thing I've got is
我需要查询属于某个产品并具有某种状态的所有故事。我得到的最接近的东西是
SELECT map.*, story.*
FROM productstorymap map, stories story
WHERE map.productId=1 AND story.status=1
But it returns me stories which don't belong to product 1. It returns something like this:
但它返回不属于产品 1 的故事。它返回如下内容：
Array
(
[0] => Array
(
[id] => 1
[productId] => 1
[storyId] => 1
[name] => Create a User Story
[content] => Admin should be able to create user stories.
[duration] => 1
[priority] => 0
[created] => 2010-09-22 17:36:21
[edited] =>
[status] => 1
)
[1] => Array
(
[id] => 4
[productId] => 1
[storyId] => 1
[name] => Bar
[content] => Xyzzy!
[duration] =>
[priority] => 1
[created] => 2011-02-10 17:50:56
[edited] => 2011-02-10 17:50:56
[status] => 1
)
)
Even though productstorymap has only two rows:
即使 productstorymap 只有两行：
ID prodcutId storyID
1 1 1
2 7 4
I'm using MySQL and PHP, if that is relevant.
如果相关，我正在使用 MySQL 和 PHP。
回答by FrustratedWithFormsDesigner
I think you were forgetting to join them on the common field, which I assumeis productstorymap.storyIDand stories.id
我想你忘记在共同领域加入他们，我认为是productstorymap.storyID和stories.id
SELECT map.*, story.*
FROM productstorymap map, stories story
WHERE map.productId=1
AND story.status=1
and map.storyID = story.id
Join-syntax can also look like this:
Join-syntax 也可以是这样的：
SELECT map.*, story.*
FROM productstorymap map
JOIN stories story on map.storyID = story.id
WHERE map.productId=1
AND story.status=1;
回答by Aivan Monceller
SELECT *
FROM productstorymap prod
JOIN stories story ON prod.storyId = story.Id
WHERE prod.productId = 1
AND story.status = 1
回答by ACdev
SELECT map.*, story.*
FROM productstorymap map, stories story
WHERE map.productId=1 AND story.status=1 AND map.storyId = story.Id
This is call a JOIN between table. Look for it
这称为表之间的 JOIN。寻找它
