SQL join two tables related by a composite columns primary key or foreign key

In this page we are discussing such a join, where there is no relationship between two participating tables.

Example:

Sample table: despatch

Sample table: orders

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

See our Model Database

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