SQL join two tables related by a composite columns primary key or foreign key
Introduction
This page delves into the concept of joining two tables in SQL based on composite columns as primary key and foreign key pairs. Unlike typical joins that rely on direct relationships between tables, this example demonstrates a scenario where the join condition is based on matching values in a specific column (ord_amount) across two unrelated tables: despatch and orders.
This type of join can be useful in situations where tables do not share explicit foreign key relationships but have overlapping data points in certain columns.
Example:
Sample table: despatchDES_NUM DES_DATE DES_AMOUNT ORD_NUM ORD_DATE ORD_AMOUNT AGENT_CODE ---------- --------- ---------- ---------- --------- ---------- ---------- D002 10-JUN-08 2000 200112 30-MAY-08 2000 A007 D005 19-OCT-08 4000 200119 16-SEP-08 4000 A010 D001 12-JAN-08 3800 200113 10-JUN-08 4000 A002 D003 25-OCT-08 900 200117 20-OCT-08 800 A001 D004 20-AUG-08 450 200120 20-JUL-08 500 A002 D006 24-JUL-08 4500 200128 20-JUL-08 3500 A002
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
...........
To get 'des_num' and 'des_date' columns from the table 'despatch' and sum of 'ord_amount' column from the table 'orders' together after a joining, with following conditions -
1. 'a', and 'b' are the aliases of 'despatch' and 'orders',
2. 'ord_amount' of 'despatch' and 'orders' must be same,
3. the same combination of 'des_num' and 'des_date' of 'despatch' should be grouped,
the following SQL statement can be used:
SQL Code:
-- Selecting specific columns: 'des_num' and 'des_date' from the 'despatch' table, and the sum of 'ord_amount' from the 'orders' table
SELECT a.des_num, a.des_date, SUM(b.ord_amount)
-- Performing a Cartesian product (cross join) between the 'despatch' and 'orders' tables (implicit join)
FROM despatch a, orders b
-- Defining the join condition in the WHERE clause where 'a.ord_amount' equals 'b.ord_amount'
WHERE a.ord_amount = b.ord_amount
-- Grouping the result set by 'des_num' and 'des_date'
GROUP BY a.des_num, a.des_date;
Explanation:
- This SQL query retrieves data from two tables: 'despatch' and 'orders'.
- It selects specific columns from the 'despatch' table: 'des_num' and 'des_date', along with the sum of 'ord_amount' from the 'orders' table.
- The query performs a Cartesian product (cross join) between the 'despatch' and 'orders' tables, implicitly joining every row from the 'despatch' table with every row from the 'orders' table.
- The join condition is specified in the WHERE clause, where 'a.ord_amount' (from 'despatch') must equal 'b.ord_amount' (from 'orders'). This join condition seems incorrect as it's comparing 'ord_amount' from two different tables, which may not result in meaningful data.
- The result set is then grouped by 'des_num' and 'des_date' using the GROUP BY clause. This ensures that the sum of order amounts is calculated for each unique combination of dispatch number and date.
- Due to the join condition being incorrect, this query may not produce the desired results. It seems to be attempting to sum order amounts based on some common value in the 'ord_amount' column, which is likely incorrect.
- It's important to review and correct the join condition to ensure that the query accurately retrieves the desired data.
- This query is intended to calculate the total order amount for each dispatch number and date combination, but it requires correction to function properly.
Output:
DES_NUM DES_DATE SUM(B.ORD_AMOUNT) -------- --------- ----------------- D004 20-AUG-08 3000 D002 10-JUN-08 10000 D005 19-OCT-08 16000 D001 12-JAN-08 16000 D003 25-OCT-08 800 D006 24-JUL-08 10500
Here is a new document which is a collection of questions with short and simple answers, useful for learning SQL as well as for interviews.
Check out our 1000+ SQL Exercises with solution and explanation to improve your skills.
Previous: Join two tables related by a single column primary key or foriegn key pair
Next: Join three or more tables based on a parent-child relationship