th0th th0th - 4 months ago 7
MySQL Question

MySQL create view joining two tables

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
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

Answer

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