How to avoid duplicate rows in my SELECT statement…

I am using SQL Server to select from multiple tables but it duplicates the rows this is my SELECT statement:

select 
    Lab_orders_Cash.cash_order_id as 'Order Number',
    Patients.Patient_No as 'Patient No',
    Patients.Patient_Name as 'Patient Name',
    Patients.Age as 'Patient Age',
    labtests.testid as 'testid',
    LabTests.TestName as 'Test Name',
    Lab_orders_Cash.order_date,
    Lab_orders_Cash.USER_ID as 'Requested By' ,
    Lab_Hematology_Samples.COLLECTION_DATE as 'Collection Date',
    Lab_Hematology_Samples.COLLECTED_BY as 'Collected By' ,
    Lab_Hematology_Samples.RECEIVING_DATE as 'Receiving Date' ,
    Lab_Hematology_Samples.RECEIVED_BY as 'Received By' 
from 
    Lab_orders_Cash
inner join 
    Order_Details_Cash on  Lab_orders_Cash.cash_order_id = Order_Details_Cash.CASH_ORDER_ID
inner join 
    patients on  Lab_orders_Cash.patient_no = Patients.Patient_No
inner join 
    LabTests on  Order_Details_Cash.TESTID=LabTests.TestId 
inner join 
    Lab_Hematology_Samples on Lab_orders_Cash.cash_order_id= Lab_Hematology_Samples.ORDER_ID
where 
    Lab_orders_Cash.patient_no = 32

I have in this select 1 cash_order_id with 2 items in Lab_Hematology_Samples its duplicates when I added this inner join

inner join Lab_Hematology_Samples on Lab_orders_Cash.cash_order_id= Lab_Hematology_Samples.ORDER_ID

it shows each item 2 times 2 rows because the order include 2 items if order include 3 items its show each item 3 times how can I avoid duplicates from my select, the primary key was id column in each table?

Answer

You can use select distinct to avoid duplicates in your result. However, this operation will have a negative effect on your runtime (especially when working with big databases), so you should only use it, if there can be duplicates.

How to avoid duplicate rows in my SELECT statement from multiple tables?

Advertisement

Answer