2 questions, include all records, and .......

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

MS Access

2 questions, include all records, and .......

Author

Topic

MikeB
Constraint Violating Yak Guru

387 Posts

Posted - 2003-10-31 : 16:36:04

I have 2 questions on the following:


tblProjectInformation
Project | Name     | City
10001   | My House | MyTown

tblLoadInformation
Date    | Project  | Load
10/31/03| 10001    | 1
10/31/03| 10001    | 2
10/31/03| 10001    | 3

tblComponentSchedule
Project | ShipDate | Load | Component 
10001   | 10/31/03 | 1    | 401       
10001   | 10/31/03 | 1    | 401

tblComponents
Project | Component | Weight 
10001   | 401       | 1000

*******************************
Output Required
*******************************
Date     |Project   | Load   | TotalWeight 
10/31/03 |10001     | 1      | 2000
10/31/03 |10001     | 2      | 0
10/31/03 |10001     | 3      | 0

The output I want is the whole table as shown above but the output I get is what is in blue. If the sum(tblComponents.Weight) = 0 then now record is listed. The following is the SQL

First Question: How can I ensure all records from tblLoadInformation are listed!


SELECT tblLoadInformation.Project,
       tblLoadInformation.Load,
       tblLoadInformation.Date,
       Sum([tblComponents].[Weight]) AS TotalWeight
FROM (tblLoadInformation INNER JOIN tblComponentSchedule 
     ON (tblLoadInformation.Load = tblComponentSchedule.Load) 
     AND (tblLoadInformation.Date = tblComponentSchedule.ShippingDate) 
     AND (tblLoadInformation.Project = tblComponentSchedule.Project)) 
     INNER JOIN tblComponents 
         ON (tblComponentSchedule.Component = tblComponents.Component) 
         AND (tblComponentSchedule.Project = tblComponents.Project)
GROUP BY tblLoadInformation.Project, tblLoadInformation.Load, tblLoadInformation.Date

Second Question: If I inner join the tblProjectInforation table so I can get the project information, I have to add the field names to the group by clause or I will get You tried to execute a query that doesn't include the specified expression 'Name' as part of an aggregate function'

Any ideas, suggestions?

Mike B

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-10-31 : 17:42:31

For the 1st question:

try to replace the both INNER JOINs with LEFT JOINs.

MikeB
Constraint Violating Yak Guru

387 Posts

Posted - 2003-11-01 : 07:31:49

That worked. Thanks :)

Mike B

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources