Why Do Multiple-Table Joins Produce Duplicate Rows

Why do multiple-table joins produce duplicate rows?

If one of the tables M, S, D, or H has more than one row for a given Id (if just the Id column is not the Primary Key), then the query would result in "duplicate" rows. If you have more than one row for an Id in a table, then the other columns, which would uniquely identify a row, also must be included in the JOIN condition(s).

References:

Related Question on MSDN Forum

JOIN results give duplicate rows - SQL Server

Just add group by clause with sum aggregation function

SELECT I.InvoiceNumber, I.AmountPaid,
sum(O.Amount) Amount
FROM OrderInvoice O
JOIN Invoice I 
ON O.InvoiceNumber = I.InvoiceNumber group by I.InvoiceNumber, I.AmountPaid

Result :

InvoiceNumber   AmountPaid  Amount
123             10          10
567             10          10

Why do 3 tables join produce duplicate rows?

Created sample data with 3 table Vehicletype, VehicleOwner, VehicleInformation in which typeID is PK.

/* Create a table */

CREATE TABLE Vehicletype(Id integer PRIMARY KEY, Name text);
CREATE TABLE VehicleOwner(OwnerId integer, InfoID integer, TypeId integer, Name text);
CREATE TABLE VehicleInformation(InfoId integer, OwnerId integer, TypeId integer, INfo text);

/* Create few records in Vehicletype table */

INSERT INTO Vehicletype VALUES(1,'TYPE1');
INSERT INTO Vehicletype VALUES(2,'TYPE2');
INSERT INTO Vehicletype VALUES(3,'TYPE3');

/* Create few records in VehicleOwner table */

INSERT INTO VehicleOwner VALUES(1,1,1,'NAME1');
INSERT INTO VehicleOwner VALUES(2,2,2,'NAME2');
INSERT INTO VehicleOwner VALUES(3,3,3,'NAME3');
INSERT INTO VehicleOwner VALUES(4,4,1,'NAME4');
INSERT INTO VehicleOwner VALUES(5,5,2,'NAME5');
INSERT INTO VehicleOwner VALUES(6,6,3,'NAME6');
INSERT INTO VehicleOwner VALUES(7,7,1,'NAME7');

/* Create few records in VehicleInformation table */

INSERT INTO VehicleInformation VALUES(1,1,1,'INFO1');
INSERT INTO VehicleInformation VALUES(2,2,2,'INFO2');
INSERT INTO VehicleInformation VALUES(3,3,3,'INFO3');
INSERT INTO VehicleInformation VALUES(4,4,1,'INFO4');
INSERT INTO VehicleInformation VALUES(5,5,2,'INFO5');
INSERT INTO VehicleInformation VALUES(6,6,3,'INFO6');
INSERT INTO VehicleInformation VALUES(7,7,1,'INFO7');

COMMIT;

/* Display all the records from the table */

SELECT * FROM Vehicletype;
SELECT * FROM VehicleOwner;
SELECT * FROM VehicleInformation;

This join will give you the unique result from your data.

select *
from  Vehicletype vt,VehicleOwner vo, VehicleInformation vi
where 1=1
and vt.id=vo.typeid
and vt.id=vi.typeid
and vo.ownerid=vi.ownerid
and vo.infoid=vi.infoid
and vo.typeid=vi.typeid;

Duplicate rows while multiplying two columns of two different tables using joins

You need to get the question_marks of each segment. Also, you should JOIN using the branch_id, test_id, subject_code_id along with the segment_id.

Using JOIN and then take Distinct values

SELECT DISTINCT qsm.segment_id, segment_name, segment_description
  , must_attend_question AS tot_attented_question, total_question AS tot_questions
  , (qsd.question_marks * must_attend_question) AS tot_marks
  , '' AS marks_obtain
FROM dbo.Question_Segment_Master AS qsm
INNER JOIN dbo.Question_Set_Details AS qsd
ON (qsd.branch_id = qsm.branch_id AND qsd.test_id = qsm.test_id
AND qsd.segment_id = qsm.segment_id AND qsd.subject_code_id = qsm.subject_code_id)
WHERE qsm.subject_code_id = 1 and qsm.test_id = 1 and qsm.branch_id = 15;

Demo

---

Related Topics