SQL join tables based on non-key column

Last update on May 14 2024 13:06:04 (UTC/GMT +8 hours)

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

Example:

Sample table: despatch

DES_NUM    DES_DATE  DES_AMOUNT    ORD_NUM ORD_DATE  ORD_AMOUNT AGENT_CODE
---------- --------- ---------- ---------- --------- ---------- ----------
D002       10-JUN-08       2000     200112 30-MAY-08       2000 A007
D005       19-OCT-08       4000     200119 16-SEP-08       4000 A010
D001       12-JAN-08       3800     200113 10-JUN-08       4000 A002
D003       25-OCT-08        900     200117 20-OCT-08        800 A001
D004       20-AUG-08        450     200120 20-JUL-08        500 A002
D006       24-JUL-08       4500     200128 20-JUL-08       3500 A002

Sample table: orders

   ORD_NUM ORD_AMOUNT ADVANCE_AMOUNT ORD_DATE  CUST_CODE       AGENT_CODE      ORD_DESCRIPTION
---------- ---------- -------------- --------- --------------- --------------- -----------------
    200114       3500           2000 15-AUG-08 C00002          A008
    200122       2500            400 16-SEP-08 C00003          A004
    200118        500            100 20-JUL-08 C00023          A006
    200119       4000            700 16-SEP-08 C00007          A010
    200121       1500            600 23-SEP-08 C00008          A004
    200130       2500            400 30-JUL-08 C00025          A011
    200134       4200           1800 25-SEP-08 C00004          A005
    200108       4000            600 15-FEB-08 C00008          A004
    200103       1500            700 15-MAY-08 C00021          A005
    200105       2500            500 18-JUL-08 C00025          A011
    200109       3500            800 30-JUL-08 C00011          A010
    200101       3000           1000 15-JUL-08 C00001          A008
    200111       1000            300 10-JUL-08 C00020          A008
    200104       1500            500 13-MAR-08 C00006          A004
    200106       2500            700 20-APR-08 C00005          A002
    200125       2000            600 10-OCT-08 C00018          A005
    200117        800            200 20-OCT-08 C00014          A001
    200123        500            100 16-SEP-08 C00022          A002
    200120        500            100 20-JUL-08 C00009          A002
    200116        500            100 13-JUL-08 C00010          A009
    200124        500            100 20-JUN-08 C00017          A007
    200126        500            100 24-JUN-08 C00022          A002
    200129       2500            500 20-JUL-08 C00024          A006
    200127       2500            400 20-JUL-08 C00015          A003
    200128       3500           1500 20-JUL-08 C00009          A002
    200135       2000            800 16-SEP-08 C00007          A010
    200131        900            150 26-AUG-08 C00012          A012
    200133       1200            400 29-JUN-08 C00009          A002
    200100       1000            600 08-JAN-08 C00015          A003
    200110       3000            500 15-APR-08 C00019          A010
    200107       4500            900 30-AUG-08 C00007          A010
    200112       2000            400 30-MAY-08 C00016          A007
    200113       4000            600 10-JUN-08 C00022          A002
    200102       2000            300 25-MAY-08 C00012          A012

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 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 three or more tables based on a parent-child relationship
Next: Understanding Subqueries