Duplicates in a LEFT JOIN

Question

I am using LEFT JOIN to join two tables but it is returning a few duplicates. This is my query:

SELECT tblDrill.Hole_ID, tblAssay.MidPoint, tblAssay.SampleNumber, tblAssay.Gold, tblMagSus.MagSus
FROM tblDrill LEFT JOIN tblMagSus ON (((tblAssay.MidPoint)>tblMagSus.From And (tblAssay.MidPoint)<tblMagSus.To)) AND (tblAssay.Hole_ID = tblMagSus.Hole_ID);

A few times, there is more than one tblAssay.MidPoint that falls between the tblMagSus.From and tblMagSus.To so it returns two records. I only want it to return the record that contains the highest MagSus value.

Could you clarify what you are querying the database for and what is stored in each table? — Owen Delahoy, Jul 16 '13 at 19:53

Owen Delahoy · Answer 1 · 2013-07-15T23:18:17.553

0

Perhaps SELECT DISTINCT would be appropriate

http://www.w3schools.com/sql/sql_distinct.asp

SELECT DISTINCT tblDrill.Hole_ID, tblAssay.MidPoint, tblAssay.SampleNumber, tblAssay.Gold, tblMagSus.MagSus
FROM tblDrill LEFT JOIN tblMagSus ON (((tblAssay.MidPoint)>tblMagSus.From And (tblAssay.MidPoint)<tblMagSus.To)) AND (tblAssay.Hole_ID = tblMagSus.Hole_ID);

edited Jul 15 '13 at 23:18

answered Jul 15 '13 at 23:09

Owen Delahoy

806
6
22

I have tried the SELECT DISTINCT option but I far as I understand, SELECT DISTINCT identifies entire rows that are distinct and I am looking for just tblDrill.SampleNumber that are distinct. – LIVIKA Jul 16 '13 at 17:10

Duplicates in a LEFT JOIN

1 Answers1