Join query output on one line 1

[guitarzan]

I am having troubles figuring out the best way to approach writing a query. A simplified version of my database is as follows:
Tests table:

TestID   CatalogNo	
1        1002-11
2        1002-11
3        1002-12

TestData table:

TestID  Volume    Mean
1       5         4.121
1       50        50.211
2       5         4.88
2       50        50.21
3       10        10.02
3       100       100.361

The query I need:

TestID  CatalogNo  LoVol  LoVolMean  HiVol  HiVolMean
1       1002-11    5	  4.121      50     50.211
2       1002-11    5      4.88       50     50.21
3       1002-12    10     10.02      100    100.361

…which basically combines the two tables, and displays them onto a single row. I guess what I need is a subquery that extracts the information from TestData where TestID matches, ordered by Volume, and fill the LoVol/LoVolMean fields with the first record found, then fill the HiVol/HiVolMean fields with the next record (or with blanks if there is no second record). I’m just not sure how to proceed. Does anyone have any suggestions?

 

[M8KWR]

I think you require something like this..

I have name my tables Test and TestData, in case you need to rename them in the code, hope this helps.

SELECT testData.TestID, Test.CatalogNo, Min(testData_1.Volume) AS LoVol, Min(testData_1.mean) AS LoVolMean, Max(testData.Volume) AS HiVol, Max(testData.mean) AS HiVolMean
FROM (testData INNER JOIN testData AS testData_1 ON testData.TestID = testData_1.TestID) INNER JOIN Test ON testData_1.TestID = Test.TestID
GROUP BY testData.TestID, Test.CatalogNo;

 

[guitarzan]

Nice solution In fact, there are cases where there are more than two records per test... but I'm only interested in the lowest and highest, so Min and Max should work perfectly.

Thanks for your help!

 

[PHV]

Why using testData 2 times ?
SELECT testData.TestID, Test.CatalogNo, Min(testData.Volume) AS LoVol, Min(testData.mean) AS LoVolMean, Max(testData.Volume) AS HiVol, Max(testData.mean) AS HiVolMean
FROM testData INNER JOIN Test ON testData.TestID = Test.TestID
GROUP BY testData.TestID, Test.CatalogNo

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886