query/view question - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

query/view question

Author

Topic

roncdf
Starting Member

13 Posts

Posted - 2006-11-10 : 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

30421 Posts

Posted - 2006-11-10 : 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 - 2006-11-10 : 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

30421 Posts

Posted - 2006-11-10 : 05:04:34

Yes.

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-10 : 05:06:52

[code]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[/code]

Peter Larsson
Helsingborg, Sweden

roncdf
Starting Member

13 Posts

Posted - 2006-11-10 : 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 - 2006-11-10 : 05:19:42

Ah well, i can adjust the query using the quantitytable in the code.
Thx for the help!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources