w3resource

SQL creating view with JOIN

View with JOIN

In this page, we are going to discuss, how two or more tables can be involved and join themselves to make a view in CREATE VIEW statement.

Example:

Sample table: orders


Sample table: customer


Sample table: agents


To create a view 'ordersview' by three tables 'orders', 'customer' and ' agents' with following conditions -

1. 'a' and 'b' and 'c' are the aliases of 'orders' and 'customer' and 'agents' table,

2. 'cust_code' of 'orders' and 'customer' table must be same,

3. 'agent_code' of 'orders' and 'agents' table must be same,

the following SQL statement can be used:


-- Creating a view named ordersview
CREATE VIEW ordersview
-- Defining the view's query to select specific columns
-- from the orders, customer, and agents tables
-- and joining them based on cust_code and agent_code columns
AS SELECT ord_num, ord_amount, a.agent_code,
          agent_name, cust_name
-- Selecting ord_num, ord_amount, agent_code, agent_name, and cust_name columns
FROM orders a, customer b, agents c
-- Joining the orders, customer, and agents tables based on cust_code and agent_code columns
WHERE a.cust_code = b.cust_code
AND a.agent_code = c.agent_code;

Explanation:

  • This SQL code creates a view named "ordersview".

  • Views are virtual tables that represent the result of a stored query.

  • The CREATE VIEW statement is used to define a new view.

  • In this case, the view "ordersview" is defined by selecting specific columns (ord_num, ord_amount, agent_code, agent_name, and cust_name) from the orders, customer, and agents tables.

  • The orders, customer, and agents tables are joined based on the cust_code and agent_code columns.

  • The WHERE clause specifies the conditions for the join, ensuring that the cust_code and agent_code columns match between the tables.

  • Once created, the view "ordersview" will contain information about orders, including the order number, order amount, agent code, agent name, and customer name. This view can be queried like a regular table, providing a convenient way to access this combined subset of data.

Output:

Sql creating view with having

To execute query on this view

SELECT * FROM ordersview;

See our Model Database

Check out our 1000+ SQL Exercises with solution and explanation to improve your skills.

Previous: Create view with aggregate functions count(), sum() and avg()
Next: Update View



Follow us on Facebook and Twitter for latest update.