MySQL创建连接两个表的视图
问题描述:
如何创建将不同列与不同表合并的视图?我有三个表,例如:用户,物品和礼物(在此示例中,这是一个系统,用户可以将礼物赠送给另一个用户)
How can I create a view that merges different columns with a different table? I have three tables for example: users, items and gifts (in this example it's a system that a user can give a gift to another user)
users
表包含有关用户的信息,items
表包含有关项目的信息,gifts
表显示哪个用户向哪个用户发送了什么礼物.
users
table has information about users, items
table has information about items and gifts
table shows which user sent what gift to which user.
我想要创建一个如下所示的视图:
What I want is to create a view like following:
user_from | user_to | gift_name | gift_price
sally | john | Teddy Bear | 10
答
您必须首先将三个表连接起来.例子
You must join the three tables first. Example
CREATE VIEW GiftsList
AS
SELECT b.name user_from,
c.name user_to,
d.name gift_name,
d.price gift_price
FROM gift a
INNER JOIN users b
ON a.user_from = b.id
INNER JOIN users c
ON a.user_from = c.id
INNER JOIN items d
ON a.item = d.id