SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 query/view question
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

roncdf
Starting Member

13 Posts

Posted - 11/10/2006 :  04:18:49  Show Profile  Reply with Quote
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
29908 Posts

Posted - 11/10/2006 :  04:31:05  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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
Go to Top of Page

roncdf
Starting Member

13 Posts

Posted - 11/10/2006 :  05:03:08  Show Profile  Reply with Quote
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?
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29908 Posts

Posted - 11/10/2006 :  05:04:34  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Yes.


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29908 Posts

Posted - 11/10/2006 :  05:06:52  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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
Go to Top of Page

roncdf
Starting Member

13 Posts

Posted - 11/10/2006 :  05:12:14  Show Profile  Reply with Quote
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.
Go to Top of Page

roncdf
Starting Member

13 Posts

Posted - 11/10/2006 :  05:19:42  Show Profile  Reply with Quote
Ah well, i can adjust the query using the quantitytable in the code.
Thx for the help!
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000