MYSQL JOINING TWO TABLES Problem which display dublicate id-CodePudding

Am trying to learn more on mysql database and I came one problem. I have two tables

consumables

 ------------ ----------- ----------------------- ------ ------------------- ------ 
| dod        | item_code | item_description      | dept | quantity_received | unit |
 ------------ ----------- ----------------------- ------ ------------------- ------ 
| 2021-12-16 | Jell001   | Petroleum Jelly       | HBT  |                35 | Pcs  |
| 2021-12-16 | ELM001    | Consumer Control Unit | EWM  |                15 | Pcs  |
| 2021-12-17 | ELM002    | D3210 Contactor       | EWM  |                23 | Pcs  |
| 2021-12-17 | ICT001    | Carburator            | ICT  |                23 | Pcs  |
| 2021-12-17 | ICT001    | Carburator            | ICT  |                23 | Pcs  |
| 2021-12-18 | ELM001    | Consumer Control Unit | EWM  |                15 | Pcs  |
 ------------ ----------- ----------------------- ------ ------------------- ------

issue_consumables table

 ---- ---------- ------------ -------------- ----------- ----------------- ------ 
| id | username | doe        | issued_to    | item_code | quantity_issued | unit |
 ---- ---------- ------------ -------------- ----------- ----------------- ------ 
|  1 | STAMP    | 2021-12-18 | John Doe     | ELM001    |               4 | Pcs  |
|  2 | STAMP    | 2021-12-18 | John Doe     | ELM002    |               5 | Pcs  |
|  3 | STAMP    | 2021-12-18 | John Doe     | ICT001    |              35 | Pcs  |
|  4 | STAMP    | 2021-12-15 | Jessy Jesica | Jell001   |              20 | Pcs  |
 ---- ---------- ------------ -------------- ----------- ----------------- ------

My desired Results

 ---- ----------- ------------ -------------- ----------------------- ----------------- 
| id | item_code | date1      | issued_to    | item_description      | quantity_issued |
 ---- ----------- ------------ -------------- ----------------------- ----------------- 
|  4 | Jell001   | 15-12-2021 | Jessy Jesica | Petroleum Jelly       |              20 |
|  3 | ICT001    | 18-12-2021 | John Doe     | Carburator            |              35 |
|  2 | ELM002    | 18-12-2021 | John Doe     | Consumer Control Unit |               5 |
|  1 | ELM001    | 18-12-2021 | John Doe     | Petroleum Jelly       |               4 |
 ---- ----------- ------------ -------------- ----------------------- -----------------

My Query is

SELECT
    issue_consumables.id,
    issue_consumables.item_code,
    DATE_FORMAT(issue_consumables.doe,'%d-%m-%Y')as date1,
    issue_consumables.issued_to,
    consumables.item_description,
    issue_consumables.quantity_issued
    FROM consumables
    RIGHT JOIN issue_consumables ON consumables.item_code = issue_consumables.item_code
    ORDER BY issue_consumables.id DESC

the Results am getting

 ---- ----------- ------------ -------------- ----------------------- ----------------- 
| id | item_code | date1      | issued_to    | item_description      | quantity_issued |
 ---- ----------- ------------ -------------- ----------------------- ----------------- 
|  4 | Jell001   | 15-12-2021 | Jessy Jesica | Petroleum Jelly       |              20 |
|  3 | ICT001    | 18-12-2021 | John Doe     | Carburator            |              35 |
|  3 | ICT001    | 18-12-2021 | John Doe     | Carburator            |              35 |
|  2 | ELM002    | 18-12-2021 | John Doe     | D3210 Contactor       |               5 |
|  1 | ELM001    | 18-12-2021 | John Doe     | Consumer Control Unit |               4 |
|  1 | ELM001    | 18-12-2021 | John Doe     | Consumer Control Unit |               4 |
 ---- ----------- ------------ -------------- ----------------------- -----------------

where am I doing Wrong to get the desired results

CodePudding user response：

You need to have a set of unique item_code and its description in order to achieve the result.

Something like:

select issue_consumables.id,
    issue_consumables.item_code,
    DATE_FORMAT(issue_consumables.doe,'%d-%m-%Y')as date1,
    issue_consumables.issued_to,
    consumables.item_description,
    issue_consumables.quantity_issued
    FROM issue_consumables
join ( select distinct item_code, item_description from consumables ) consumables on consumables.item_code = issue_consumables.item_code

to include quantity_received

select issue_consumables.id,
    issue_consumables.item_code,
    DATE_FORMAT(issue_consumables.doe,'%d-%m-%Y')as date1,
    issue_consumables.issued_to,
    consumables.item_description,
    issue_consumables.quantity_issued,
    consumables.quantity_received
    FROM issue_consumables
join ( select item_code, item_description, sum(quantity_received) quantity_received from consumables group by item_code, item_description ) consumables on consumables.item_code = issue_consumables.item_code