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
ORD_NUM ORD_AMOUNT ADVANCE_AMOUNT ORD_DATE CUST_CODE AGENT_CODE ORD_DESCRIPTION
---------- ---------- -------------- --------- --------------- --------------- -----------------
200114 3500 2000 15-AUG-08 C00002 A008
200122 2500 400 16-SEP-08 C00003 A004
200118 500 100 20-JUL-08 C00023 A006
200119 4000 700 16-SEP-08 C00007 A010
200121 1500 600 23-SEP-08 C00008 A004
200130 2500 400 30-JUL-08 C00025 A011
200134 4200 1800 25-SEP-08 C00004 A005
200108 4000 600 15-FEB-08 C00008 A004
200103 1500 700 15-MAY-08 C00021 A005
200105 2500 500 18-JUL-08 C00025 A011
.........
200102 2000 300 25-MAY-08 C00012 A012
Sample table: customer
+-----------+-------------+-------------+--------------+--------------+-------+-------------+-------------+-------------+---------------+--------------+------------+ |CUST_CODE | CUST_NAME | CUST_CITY | WORKING_AREA | CUST_COUNTRY | GRADE | OPENING_AMT | RECEIVE_AMT | PAYMENT_AMT |OUTSTANDING_AMT| PHONE_NO | AGENT_CODE | +-----------+-------------+-------------+--------------+--------------+-------+-------------+-------------+-------------+---------------+--------------+------------+ | C00013 | Holmes | London | London | UK | 2 | 6000.00 | 5000.00 | 7000.00 | 4000.00 | BBBBBBB | A003 | | C00001 | Micheal | New York | New York | USA | 2 | 3000.00 | 5000.00 | 2000.00 | 6000.00 | CCCCCCC | A008 | | C00020 | Albert | New York | New York | USA | 3 | 5000.00 | 7000.00 | 6000.00 | 6000.00 | BBBBSBB | A008 | | C00025 | Ravindran | Bangalore | Bangalore | India | 2 | 5000.00 | 7000.00 | 4000.00 | 8000.00 | AVAVAVA | A011 | | C00024 | Cook | London | London | UK | 2 | 4000.00 | 9000.00 | 7000.00 | 6000.00 | FSDDSDF | A006 | | C00015 | Stuart | London | London | UK | 1 | 6000.00 | 8000.00 | 3000.00 | 11000.00 | GFSGERS | A003 | | C00002 | Bolt | New York | New York | USA | 3 | 5000.00 | 7000.00 | 9000.00 | 3000.00 | DDNRDRH | A008 | | C00018 | Fleming | Brisban | Brisban | Australia | 2 | 7000.00 | 7000.00 | 9000.00 | 5000.00 | NHBGVFC | A005 | | C00021 | Jacks | Brisban | Brisban | Australia | 1 | 7000.00 | 7000.00 | 7000.00 | 7000.00 | WERTGDF | A005 | .......... | C00011 | Sundariya | Chennai | Chennai | India | 3 | 7000.00 | 11000.00 | 7000.00 | 11000.00 | PPHGRTS | A010 | +-----------+-------------+-------------+--------------+--------------+-------+-------------+-------------+-------------+---------------+--------------+------------+
Sample table: agents
+------------+----------------------+--------------------+------------+-----------------+---------+ | AGENT_CODE | AGENT_NAME | WORKING_AREA | COMMISSION | PHONE_NO | COUNTRY | +------------+----------------------+--------------------+------------+-----------------+---------+ | A007 | Ramasundar | Bangalore | 0.15 | 077-25814763 | | | A003 | Alex | London | 0.13 | 075-12458969 | | | A008 | Alford | New York | 0.12 | 044-25874365 | | | A011 | Ravi Kumar | Bangalore | 0.15 | 077-45625874 | | | A010 | Santakumar | Chennai | 0.14 | 007-22388644 | | | A012 | Lucida | San Jose | 0.12 | 044-52981425 | | | A005 | Anderson | Brisban | 0.13 | 045-21447739 | | | A001 | Subbarao | Bangalore | 0.14 | 077-12346674 | | | A002 | Mukesh | Mumbai | 0.11 | 029-12358964 | | | A006 | McDen | London | 0.15 | 078-22255588 | | | A004 | Ivan | Torento | 0.15 | 008-22544166 | | | A009 | Benjamin | Hampshair | 0.11 | 008-22536178 | | +------------+----------------------+--------------------+------------+-----------------+---------+
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:
To execute query on this view
SELECT * FROM ordersview;
Check out our 1000+ SQL Exercises with solution and explanation to improve your skills.
PREV : Create view with aggregate functions count(), sum() and avg()
NEXT : Update View