Returning only one record in a "1-to-many" relation

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

Other Forums

Other Topics

Returning only one record in a "1-to-many" relation

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2006-09-07 : 09:56:06

Martijn writes "I'm working on a report that should show only 1 record per 'productionorder'. The result should be something like:

P060010243 240.00

Where "P060010243" is the productionorder number and 240.00 is the SUM of several (in this case 4) underlying operations-hours.
There are 2 tables that are needed: T_ProductionHeader and T_ProdBillOfOper. The primary key in T_ProductionHeader is "ProdHeaderDossierCode". This is also a primary key in T_ProdBillOfOper. Since several values for the field "Hours" may appear in T_ProdBillOfOper, this last table has another key; "LineNr".
When I link these 2 tables, 4 records appear.

It should show only the summarized number of hours but I can't get this to work. I've tried the following statement.
-------------------------------
Select
t1.ProdHeaderDossierCode,
'Hours' Select (Sum(t2.Hour) Where t1.ProdHeaderDossierCode = t2.ProdHeaderDossierCode)

From
T_ProductionHeader t1,
T_ProdBillOfOper t2

Where
t1.ProdHeaderDossierCode = t2.ProdHeaderDossierCode And
t1.ProdHeaderOrdNr = "P060010243"
-------------------------------

I recieve the following error code:
-------------------------------
Server Message: Number 107, Severity 15
Server 'VDBergServer', Line 2:
The column prefix 't1' does not match with a table name or alias name used in the query. Either the table is not specified in the FROM clause or it has a correlation name which must be used instead.
Server Message: Number 156, Severity 15
Server 'VDBergServer', Line 3:
Incorrect syntax near the keyword 'Where'.
-------------------------------

I'm using SQL Advantage 11.5 for this. It runs under Windows NT"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-07 : 09:58:58

[code]Select t1.ProdHeaderDossierCode,
Sum(t2.Hour) Hours
From T_ProductionHeader t1
Inner Join T_ProdBillOfOper t2 ON t2.ProdHeaderDossierCode = t1.ProdHeaderDossierCode
Where t1.ProdHeaderOrdNr = 'P060010243'
Gropu By t1.ProdHeaderDossierCode[/code]

Peter Larsson
Helsingborg, Sweden

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2006-09-07 : 11:36:18

I'm using SQL Advantage 11.5 for this. It runs under Windows NT"....despite this being is a MSSQLServer focused site...that code should work.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-07 : 14:47:50

Yes. But you must change my misspelling Gropu to Group.

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources