| Author |
 Topic  |
|
|
roncdf
Starting Member
13 Posts |
 Posted - 11/10/2006 : 04:18:49
|
Hello,
I need to create an overview of some Data in a system, but im not sure if this can be done with a query.
I have a table with Samples, it has a relation with a table Testresults, the table Testresults has a relation with a table Quantitys.
So a sample has a number of testresults for some (or all) quantitys.
So now i want to create a view which gives me the Sample information followed by the testresultvalue for all quantitys(if the result of the quantity is empty it should give a null), in one single row. Is this possible, if so how?
Thx |
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
30421 Posts |
Posted - 11/10/2006 : 04:31:05
|
Learn SQL. Especially JOIN-ing.
What have you manager this far? Post here the query you have now.
Also post any table DDL you have.
If you have som sample data, that would be great. If you care enough about this homework, you also post the expected result, based on the provided sample data.
Peter Larsson
Helsingborg, Sweden |
 |
|
|
roncdf
Starting Member
13 Posts |
Posted - 11/10/2006 : 05:03:08
|
First of all thx for replying, but i dont think you understand what i want. I will explain further.
Tables:
Sample:
SampleNr varchar(20)
Other
Test:
TestNr int(10)
SampleNr
Other
Result:
TestNr
QuantityNr
Value
Other
Quantity:
QuantityNr int(10)
QuantityName
I can for instance do this query:
SELECT dbo.LSample.SampleNr, dbo.LResult.Value, dbo.LQuantity.QuantityName
FROM dbo.LSample
INNER JOIN dbo.LTest ON dbo.LSample.SampleNr = dbo.LTest.SampleNr INNER JOIN dbo.LResult ON dbo.LTest.TestNr = dbo.LResult.TestNr INNER JOIN dbo.LQuantity ON dbo.LResult.QuantityID = dbo.LQuantity.QuantityID
The result of the query will be:
SampleNr: Value: QuantityName:
ETL00015 10,35 Fe
ETL00016 8,34 Cl
But i want to query to generate
SampleNr: Fe: Cl: Cr: Zn: (etc)
ETL00015 10,35 8,34 3,45 2,56
So my question, is this possible?
|
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
30421 Posts |
Posted - 11/10/2006 : 05:04:34
|
Yes.
Peter Larsson
Helsingborg, Sweden |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
30421 Posts |
Posted - 11/10/2006 : 05:06:52
|
SELECT SampleNr,
MAX(CASE WHEN QuantityName = 'Fe' THEN Value END) AS 'Fe',
MAX(CASE WHEN QuantityName = 'Cl' THEN Value END) AS 'Cl',
...
...
FROM (
SELECT dbo.LSample.SampleNr,
dbo.LResult.Value,
dbo.LQuantity.QuantityName
FROM dbo.LSample
INNER JOIN dbo.LTest ON dbo.LSample.SampleNr = dbo.LTest.SampleNr
INNER JOIN dbo.LResult ON dbo.LTest.TestNr = dbo.LResult.TestNr
INNER JOIN dbo.LQuantity ON dbo.LResult.QuantityID = dbo.LQuantity.QuantityID
) q
Peter Larsson
Helsingborg, Sweden |
|
|
|
roncdf
Starting Member
13 Posts |
Posted - 11/10/2006 : 05:12:14
|
| Ah yes, but i cant code in the IDs and names of the quantitys. The users of the system can add/delete/modify quantitys so this does not work. |
|
|
|
roncdf
Starting Member
13 Posts |
Posted - 11/10/2006 : 05:19:42
|
Ah well, i can adjust the query using the quantitytable in the code.
Thx for the help! |
|
|
| |
Topic |
|
query/view question