SQL join tables based on non-key column
In this page we are discussing such a join, where there is no relationship between two participating tables.
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 :
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;
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.