Inner join three tables out of which one is not connected with other-CodePudding

I have three tables client_invoices ,contract_additional_info and contract out of which client_invoices is connected with contract_additional_info and contract_additional_info is connected with contract table .

contract table and client_invoices table don't have any relations.

Now I am running following query

SELECT   client_invoices.markup_type,
         client_invoices.supplier_invoice_number, 
         client_invoices.client_payment_req_id,
         client_invoices.net_amount,
         client_invoices.markup_value,
         client_invoices.net_qty,
         client_invoices.markup_value,
         contract.clientId as buyerClientId,
         contract_additional_info.buyer_contract_id as contract_id
FROM client_invoices
INNER JOIN contract_additional_info ON contract_additional_info.contract_id =client_invoices.contract_id
INNER JOIN contract ON contract_additional_info.buyer_contract_id = contract.id  
WHERE  client_invoices.status=3 ;

It is giving me duplicate records ,how to fix the query such that it only gives unique records (unique client_invoice.supplier_invoice_number)

CodePudding user response：

Have you tried using SELECT DISTINCT? This should give you only unique records.

CodePudding user response：

Try a GROUP BY with the columns that are identified as primary items to show.

CodePudding user response：

You can start by assuming that one of the joins is returning multiple results which causes the duplicates:

Either multiple contract_additional_info returned for each invoice or multiple contracts for each contract_additional_info.

Based on their names, I would say the former is causing this. If this is true, ask yourself if that is correct. Maybe the database structure is your problem.

If it is correct and you can have the same invoice for multiple contract_additional_info entries, then GROUP BY the column(s) you expect to be unique (check that they are unique at the column level as well). E.g. supplier invoice number and/or client_invoices.contract_id.

You could also join with a SELECT DISTINCT sub query from the contract_additional_info table.