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?
Advertisement
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.