Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

MySQL create view joining two tables

Tags:

join

mysql

view

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
like image 507
Gokhan Sari Avatar asked Sep 10 '12 12:09

Gokhan Sari


People also ask

Can we create a view by joining two tables?

You can create a view that combines data from two or more tables by naming more than one table in the FROM clause. In the following example procedure, the INVENTORY_LIST table contains a column of item numbers called ITEM_NUMBER and a column of item cost called UNIT_COST.

Can we use joins in view?

Views can use joins to select data from numerous sources like tables, table functions, or even other views.

Can we join 2 views in SQL?

Use a join view to run tests on several related columns across different tables. You can create a join view instead of multiple SQL views with joins. For example, the Employee table has employee details, Inventory table has sales details, and the customer table has customer details.


1 Answers

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
like image 126
John Woo Avatar answered Oct 03 '22 13:10

John Woo