w3resource logo


SQL JOIN NONKEY COLUMNS

SQL join tables based on non-key column

Secondary Nav

Description

In this page we are discussing about 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 :

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.



Is this content useful for you?

 


You might be using Adblocker. Since w3resource does not charge anything from users, all of our expenses (e.g. Hosting, Content Creation, Development etc.) are met by advertisement. Please whitelist w3resource.