I’m attempting to retrieve records using an SQL INNER JOIN but the query returns duplicate records (under certain circumstances) and I need to eliminate this problem. Here’s what I have…
I have a table named ‘tblLOGIN’ which tracks samples as they come into the building. One record represents a single sample that has been logged in. The following are the table fields…
‘intSampleID’ = 4 digit identifier (primary key)
‘txtManufacturer’ = sample manufacturer’s name
…a few other fields with misc. info.
I have a second table named ‘tblIROC’ which tracks IROC test results. Tests are run on the samples that come into the building. One record represents a single test run that has been run on 1 or more samples. A typical test is run using 1 sample, however as many as 3 can be used in a single test. As a result here are the fields in this particular table…
‘intRunNumber’ = unique number assigned to each test (primary key)
‘intSampleID1’ = field for first sample ID tested in this run
‘intSampleID2’ = field for second sample ID tested in this run (if applicable)
‘intSampleID3’ = field for third sample ID tested in this run (if applicable)
…a few other fields with misc. info.
I have ‘1-to-many’ relationships from tblLOGIN. intSampleID to each of the tblIROC.intSampleID fields (all 3 of them.)
The purpose of my query is to retrieve all IROC test results for any certain manufacturer that I choose to search for. So in a nutshell I want to retrieve all IROC test records which contain a SampleID (of a certain manufacturer) in any of the 3 available fields for samples tested in the run. Here is the SQL statement that I’m using…
SELECT * FROM tblIROC INNER JOIN tblLOGIN ON ((tblIROC. intSampleID1 = tblLOGIN. intSampleID) OR (tblIROC. intSampleID2 = tblLOGIN. intSampleID) OR (tblIROC. intSampleID3 = tblLOGIN. intSampleID)) WHERE tblLOGIN.txtManufacturer = 'ManufacturerName';
This setup works great until I come across an IROC record that has more than one SampleID of the same manufacturer. The SQL query returns a duplicate record for each additional SampleID of the same manufacturer. I have tried adding the DISTINCTROW predicate to the SQL statement…
SELECT DISTINCTROW * FROM tblIROC INNER JOIN tblLOGIN ON ((tblIROC. intSampleID1 = tblLOGIN. intSampleID) OR (tblIROC. intSampleID2 = tblLOGIN. intSampleID) OR (tblIROC. intSampleID3 = tblLOGIN. intSampleID)) WHERE tblLOGIN.txtManufacturer = 'ManufacturerName';
…but it still returns the duplicate records. Can anyone help me solve this problem?
I have a table named ‘tblLOGIN’ which tracks samples as they come into the building. One record represents a single sample that has been logged in. The following are the table fields…
‘intSampleID’ = 4 digit identifier (primary key)
‘txtManufacturer’ = sample manufacturer’s name
…a few other fields with misc. info.
I have a second table named ‘tblIROC’ which tracks IROC test results. Tests are run on the samples that come into the building. One record represents a single test run that has been run on 1 or more samples. A typical test is run using 1 sample, however as many as 3 can be used in a single test. As a result here are the fields in this particular table…
‘intRunNumber’ = unique number assigned to each test (primary key)
‘intSampleID1’ = field for first sample ID tested in this run
‘intSampleID2’ = field for second sample ID tested in this run (if applicable)
‘intSampleID3’ = field for third sample ID tested in this run (if applicable)
…a few other fields with misc. info.
I have ‘1-to-many’ relationships from tblLOGIN. intSampleID to each of the tblIROC.intSampleID fields (all 3 of them.)
The purpose of my query is to retrieve all IROC test results for any certain manufacturer that I choose to search for. So in a nutshell I want to retrieve all IROC test records which contain a SampleID (of a certain manufacturer) in any of the 3 available fields for samples tested in the run. Here is the SQL statement that I’m using…
SELECT * FROM tblIROC INNER JOIN tblLOGIN ON ((tblIROC. intSampleID1 = tblLOGIN. intSampleID) OR (tblIROC. intSampleID2 = tblLOGIN. intSampleID) OR (tblIROC. intSampleID3 = tblLOGIN. intSampleID)) WHERE tblLOGIN.txtManufacturer = 'ManufacturerName';
This setup works great until I come across an IROC record that has more than one SampleID of the same manufacturer. The SQL query returns a duplicate record for each additional SampleID of the same manufacturer. I have tried adding the DISTINCTROW predicate to the SQL statement…
SELECT DISTINCTROW * FROM tblIROC INNER JOIN tblLOGIN ON ((tblIROC. intSampleID1 = tblLOGIN. intSampleID) OR (tblIROC. intSampleID2 = tblLOGIN. intSampleID) OR (tblIROC. intSampleID3 = tblLOGIN. intSampleID)) WHERE tblLOGIN.txtManufacturer = 'ManufacturerName';
…but it still returns the duplicate records. Can anyone help me solve this problem?
