SQL join tables based on non-key column

has average rating 7 out of 10. Total 5 users rated.

<<PreviousNext>>

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

Outputs of the said SQL statement shown here is taken by using Oracle Database 10g Express Edition

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.