Select from two tables based on table primary key

Question

I have a table representing common values for two types of records and I have another two tables to hold the data that are different from each other. The table 1 is as follows

banking

+-----------------+--------------+------+-----+---------+----------------+
| Field           | Type         | Null | Key | Default | Extra          |
+-----------------+--------------+------+-----+---------+----------------+
| id              | int(11)      | NO   | PRI | NULL    | auto_increment |
| officer         | varchar(32)  | NO   |     | NULL    |                |
| bank            | varchar(64)  | NO   |     | NULL    |                |
| branch          | varchar(64)  | NO   |     | NULL    |                |
| amount          | int(11)      | NO   |     | NULL    |                |
| date            | date         | NO   |     | NULL    |                |
| sys_date        | date         | NO   |     | NULL    |                |
| source_document | varchar(360) | NO   |     | NULL    |                |
+-----------------+--------------+------+-----+---------+----------------+

machinery_banking

+----------------+-------------+------+-----+---------+-------+
| Field          | Type        | Null | Key | Default | Extra |
+----------------+-------------+------+-----+---------+-------+
| banking_id     | int(11)     | NO   |     | NULL    |       |
| engine_no      | varchar(64) | NO   |     | NULL    |       |
| chassis_no     | varchar(64) | NO   |     | NULL    |       |
| receipt_number | varchar(64) | NO   |     | NULL    |       |
| payment_type   | int(11)     | NO   |     | NULL    |       |
+----------------+-------------+------+-----+---------+-------+

spare_parts_banking

+----------------+-------------+------+-----+---------+-------+
| Field          | Type        | Null | Key | Default | Extra |
+----------------+-------------+------+-----+---------+-------+
| banking_id     | int(11)     | NO   |     | NULL    |       |
| invoice_number | varchar(32) | NO   |     | NULL    |       |
+----------------+-------------+------+-----+---------+-------+

Each data that is entered to banking table has the other data in one of each tables machinery_banking or the spare_parts_banking.

I have the following data in the banking table

+----+---------+------------+------------+-----------+------------+------------+------------------------------------------------------------------------------------------+
| id | officer | bank       | branch     | amount    | date       | sys_date   | source_document                                                                          |
+----+---------+------------+------------+-----------+------------+------------+------------------------------------------------------------------------------------------+
|  1 | prasad  | Sampath    | Kaduruwela | 234234234 | 2017-06-28 | 2017-06-28 | image |
|  2 | prasad  | Commercial | Colombo    | 234234234 | 2017-05-28 | 2017-05-28 | image |
+----+---------+------------+------------+-----------+------------+------------+------------------------------------------------------------------------------------------+

I have the following data in the machinery_banking

+------------+-----------+------------+----------------+--------------+
| banking_id | engine_no | chassis_no | receipt_number | payment_type |
+------------+-----------+------------+----------------+--------------+
|          1 | 2324234   | NL234234   | RAN234         |            1 |
+------------+-----------+------------+----------------+--------------+

Data in the spare_parts_banking is as follows

+------------+----------------+
| banking_id | invoice_number |
+------------+----------------+
|          2 | INVRAN1        |
+------------+----------------+

I have tried the following query

SELECT a.id, a.officer, a.bank, a.branch, a.amount, a.date, a.sys_date, b.engine_no, b.chassis_no, a.source_document, c.invoice_number
FROM banking a,machinery_banking b, spare_parts_banking c
WHERE   a.id = b.banking_id OR a.id = c.banking_id;

and ended up the following result

+----+---------+------------+------------+-----------+------------+------------+-----------+------------+------------------------------------------------------------------------------------------+----------------+
| id | officer | bank       | branch     | amount    | date       | sys_date   | engine_no | chassis_no | source_document                                                                          | invoice_number |
+----+---------+------------+------------+-----------+------------+------------+-----------+------------+------------------------------------------------------------------------------------------+----------------+
|  1 | prasad  | Sampath    | Kaduruwela | 234234234 | 2017-06-28 | 2017-06-28 | 2324234   | NL234234   | http://res.cloudinary.com/randeepa-com/image/upload/v1498455394/dmhxqal8hjcthhgav0n9.jpg | INVRAN1        |
|  2 | prasad  | Commercial | Colombo    | 234234234 | 2017-05-28 | 2017-05-28 | 2324234   | NL234234   | http://res.cloudinary.com/randeepa-com/image/upload/v1498455394/dmhxqal8hjcthhgav0n9.jpg | INVRAN1        |
+----+---------+------------+------------+-----------+------------+------------+-----------+------------+------------------------------------------------------------------------------------------+----------------+

The result I wish to obtain is as follows

+----+---------+------------+------------+-----------+------------+------------+-----------+------------+------------------------------------------------------------------------------------------+----------------+
| id | officer | bank       | branch     | amount    | date       | sys_date   | engine_no | chassis_no | source_document                                                                          | invoice_number |
+----+---------+------------+------------+-----------+------------+------------+-----------+------------+------------------------------------------------------------------------------------------+----------------+
|  1 | prasad  | Sampath    | Kaduruwela | 234234234 | 2017-06-28 | 2017-06-28 | 2324234   | NL234234   | http://res.cloudinary.com/randeepa-com/image/upload/v1498455394/dmhxqal8hjcthhgav0n9.jpg |                |  
|  2 | prasad  | Commercial | Colombo    | 234234234 | 2017-05-28 | 2017-05-28 |           |            | http://res.cloudinary.com/randeepa-com/image/upload/v1498455394/dmhxqal8hjcthhgav0n9.jpg | INVRAN1        |
+----+---------+------------+------------+-----------+------------+------------+-----------+------------+------------------------------------------------------------------------------------------+----------------+

What query can I use to get this result

Answer 1

Try this:

SELECT a.id, a.officer, a.bank, a.branch, a.amount, a.date, a.sys_date, b.engine_no, b.chassis_no, a.source_document, c.invoice_number
FROM banking a,machinery_banking b, spare_parts_banking c
WHERE   a.id = b.banking_id(+) 
AND a.id = c.banking_id(+);

Answer 2

The reason, why you have data duplication is because of the OR between conditions

I believe this should work

SELECT a.id,
    a.officer,
    a.bank,
    a.branch,
    a.amount,
    a.date,
    a.sys_date,
    b.engine_no,
    b.chassis_no,
    a.source_document,
    c.invoice_number
FROM banking a
LEFT JOIN machinery_banking b
ON  a.id = b.banking_id
LEFT JOIN spare_parts_banking c
ON  a.id = c.banking_id;

Also move to new JOIN syntax, it is much easier to comprehend.

SQL Join Syntax - Reference