Correct query SQL?

[wvandenberg]

I have tblSamples containing data about samples collected from specific sites. tblSamples looks like:
[tt]
pkSampleID fkSiteID SampleName SampleDate Round
1787 598 WE73 15/03/2007 1
1786 597 WE71 15/03/2007 1
745 411 WE66 31/07/2007 4
1783 411 WE66 17/03/2007 1
1784 411 WE66 24/04/2007 2
1785 411 E66 27/06/2007 3
1884 411 WE66 07/09/2007 5
1780 596 WE65 12/03/2007 1
1781 596 WE65 30/05/2007 2
1782 596 WE65 28/06/2007 3
1777 595 WE64 12/03/2007 1
1778 595 WE64 24/04/2007 2
1779 595 WE64 28/06/2007 3
1776 594 WE63 15/03/2007 1
1775 593 WE61 15/03/2007 1
[/tt]
I am trying to create a query that will select common samples. A common sample is one that has [Round]=1 AND [Round]=2,3 OR 4. My query SQL is:

SELECT DISTINCT tblSamples.SampleName
FROM tblSamples INNER JOIN tblSamples AS tblSamples_1 ON tblSamples.SampleName = tblSamples_1.SampleName
WHERE (((tblSamples.Round)=1) AND ((tblSamples_1.Round) In (2,3,4)))
ORDER BY tblSamples.SampleName;

It looks like it is selecting the correct records but I was wonding if someone could verify for me. Also, if there is a better way, please don't hesitate.

Wendy

 

[r937]

select pkSampleID   
     , fkSiteID   
     , SampleName   
     , SampleDate   
     , Round
  from tblSamples 
 where SampleName in
       ( select SampleName
           from tblSamples 
          where Round in ( 1,2,3,4 )
         group
             by SampleName
         having sum(iif(Round=1,1,0))>0
            and sum(iif(Round in (2,3,4),1,0))>0
       )

r937.com | rudy.ca

 

[PHV]

Isn't this simpler ?
SELECT pkSampleID, fkSiteID, SampleName, SampleDate, Round
FROM tblSamples
WHERE Round = 1
AND SampleName IN (SELECT SampleName FROM tblSamples WHERE Round In (2,3,4))

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[r937]

it might be simpler, but it returns only Round=1 rows

my understanding is that all rows in the sample should be returned





r937.com | rudy.ca

 

[PHV]

Quote OP:
SELECT DISTINCT tblSamples.SampleName

 

[r937]

lol, i guess you're right

okay, this is simpler --

select SampleName
  from tblSamples 
 where Round in ( 1,2,3,4 )
group
    by SampleName
having sum(iif(Round=1,1,0))>0
   and sum(iif(Round=1,0,1))>0

r937.com | rudy.ca

 

[wvandenberg]

Thanks for your responses, I'll give it a whirl.