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

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

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

Method 2

You can create a view with two tables like:

 CREATE VIEW giftList AS
 SELECT users.user_from,users.user_to,gifts.gift_name,gifts.gift_price FROM users,gifts
 WHERE users.user_id = gifts.user_id;

The where clause is to make sure the output does not repeat.

Method 3

I believe were looking for data blending. So basically having google data studio do a JOIN statement on ids from 2 data sets

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0