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:

SELECT a.des_num,a.des_date,
SUM(b.ord_amount)
FROM despatch a, orders b
WHERE a.ord_amount=b.ord_amount
GROUP BY a.des_num,a.des_date;

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