MySQL create view joining two tables

Question

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

I couldn't add images to question, so they are here: i.sstatic.net/YCfAO.png i.sstatic.net/7AUhc.png i.sstatic.net/8W06W.png i.sstatic.net/forWs.png — Gokhan Sari
– Gokhan Sari, Commented Sep 10, 2012 at 12:48
What is the problem? The query part (ie how to fetch that data from those tables), or how to create a view in MySQL? — Jan Hančič
– Jan Hančič, Commented Sep 10, 2012 at 12:51

John Woo · Accepted Answer · 2012-09-10 13:03:11Z

26

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

edited Sep 10, 2012 at 13:03

answered Sep 10, 2012 at 12:53

John Woo

265k70 gold badges509 silver badges500 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Gokhan Sari Over a year ago

Changed FROM gift a to FROM gifts a, thought there was a typo. And I get error: #1054 - Unknown column 'b.users' in 'on clause'

John Woo Over a year ago

@th0th it's really a typo, it should be a.user_from = b.id and a.user_from = c.id

Dipanshu Mahla · Accepted Answer · 2019-06-06 15:09:33Z

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.

answered Jun 6, 2019 at 15:09

Dipanshu Mahla

1522 silver badges16 bronze badges

Comments

Nouru Muneza · Accepted Answer · 2022-03-11 19:10:18Z

0

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

answered Mar 11, 2022 at 19:10

Nouru Muneza

776 bronze badges

Collectives™ on Stack Overflow

MySQL create view joining two tables

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related