My Left Join Query in mySql gives duplicate results even after using DISTINCT?

I have tried many solutions to get rid of Duplicate results in Query but no use. Here are my tables:

sparepartorderdetails d table:

id ----- SparePartID --------OrderID -------- Qty ----- Price
101-----------47----------------84-------------2--------1500
102-----------49----------------86-------------3--------3000
103-----------52----------------86-------------5--------800
104-----------45----------------87-------------3--------450
105-----------48----------------87-------------2--------1200

and sparepartfeedback fb table:

PFID -----Review-----------Rating------- SparePartID -----OrderID 
1---------good job------------5--------------49--------------86
2---------nice product--------4--------------52--------------86

Now here is my QUERY which gives duplicate results:

SELECT DISTINCT d.SparePartID, s.Name, d.Quantity, d.Price, d.OrderID , 
       fb.PFID, fb.Review, fb.Rating
FROM sparepartorderdetails d LEFT JOIN
     sparepartfeedback fb
     ON fb.OrderID = d.OrderID INNER JOIN
     sparepart s
     ON d.SparePartID= s.SparePartID INNER JOIN
     orders o
     ON o.OrderID = d.OrderID
WHERE d.OrderID = "86"

Now these are the duplicate results Im getting:

SparePartID-----Name------Qty----Price----OrderID ----PFID ------Review---- Rating**
49 ----------- Lights ---- 3---- 3000 ------86--------1----------good job ------ 5
52 ----------- Mirrors---- 5----- 800-------86--------2----------nice product ------ 5
49 ----------- Lights ---- 3---- 3000 ------86--------1----------good job ------ 5
52 ----------- Mirrors---- 5----- 800-------86--------2----------nice product ------ 5

But this Query gives no duplicates if there are no reviews/ratings for an order like for OrderID: 87 it just correctly shows order details and NULL values for Review and Ratings. But when I run it for OrderID: 86 which has Reviews/Ratings, it gives duplicate results.

Below are results that I expect:

SparePartID-----Name------Qty----Price----OrderID ----PFID ------Review------- Rating**
49 ----------- Lights ---- 3---- 3000 ------86--------1----------good job ------- 5
52 ----------- Mirrors---- 5----- 800-------86--------2----------nice product --- 5

Answer

I think you have left out a JOIN condition on the spare part id:

SELECT d.SparePartID, s.Name, d.Quantity, d.Price, d.OrderID , 
       fb.PFID, fb.Review, fb.Rating
FROM sparepartorderdetails d LEFT JOIN
     sparepartfeedback fb
     ON fb.OrderID = d.OrderID
        fp.SparePartID = d.SparePartID INNER JOIN
     sparepart s
     ON d.SparePartID= s.SparePartID INNER JOIN
     orders o
     ON o.OrderID = d.OrderID
WHERE d.OrderID = 86

Advertisement

Answer