SELECT CHALLENGE? - 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 2005 Forums

Transact-SQL (2005)

SELECT CHALLENGE?

Author

Topic

losstww
Starting Member

13 Posts

Posted - 2008-06-16 : 14:29:32

I have a database with three tables: Employees, Assets, and Recovery.

I have the following SELECT statement:

SELECT Employees.EmployeeID, SUM(Assets.Amount) AS [Case Value:], SUM(Recovery.Recovery) AS [Recovery:]
FROM Assets INNER JOIN
Employees ON Assets.EmployeeID = Employees.EmployeeID INNER JOIN
Recovery ON Employees.EmployeeID = Recovery.EmployeeID
GROUP BY Employees.EmployeeID

Here is the challenge:

You will always have data in the Employee table, but not neccesarily in the Assets or Recovery table.

If there are no Assets or Recovery I would still like the query to show 0.00 for the Assets or Recovery for each Employees.EmployeesID

With the above SELECT statement it will only return values that have data created in each of the three tables.

How do you modify the SELECT statement so a row is returned for every Employees.EmployeeID?

Any help will be appreciated!

losstww

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-06-16 : 14:31:02

How is this a challenge? This sounds more like a homework question.

Are you familiar with OUTER JOINs?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

losstww
Starting Member

13 Posts

Posted - 2008-06-16 : 14:45:17

a Challenge for some, a walk in the park for others!

I'm new to SQL Server 2005. I'm not familiar with OUTER JOINS.

losstww

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-06-16 : 14:47:36

Change your query to use LEFT OUTER JOIN (or LEFT JOIN) instead of INNER JOIN (or JOIN).

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

losstww
Starting Member

13 Posts

Posted - 2008-06-16 : 15:32:31

Tara,

Thank you for your help. I changed the INNER JOIN to LEFT OUTER JOIN and it populated for each EmployeeID.

Question - how do I make it so a NULL field returns a 0.00 value?

I changed the default value in the original table to ((0.00)) but it still returns a "NULL."

EmployeeID 1
Case Value: 2500
Recovery: NULL

EmployeeID 2
Case Value: 500
Recovery: 500

EmployeeID 3
Case Value: NULL
Recovery: NULL

Thanks,

losstww

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-06-16 : 15:34:18

Use either the COALESCE or ISNULL functions. Check SQL Server Books Online for details.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

losstww
Starting Member

13 Posts

Posted - 2008-06-16 : 16:11:00

Tara,

Thanks for the lead. I settled on:

SELECT A1.EmployeeID, SUM(ISNULL(A2.Amount, 0.00)) AS Assets, SUM(ISNULL(A3.Recovery, 0.00)) AS Recovery
FROM dbo.Employees AS A1 LEFT OUTER JOIN
dbo.Assets AS A2 ON A1.EmployeeID = A2.EmployeeID LEFT OUTER JOIN
dbo.Recovery AS A3 ON A1.EmployeeID = A3.EmployeeID
GROUP BY A1.EmployeeID

A slow walk in the park on a sunny day can be very productive.

Thanks again,

losstww

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-17 : 00:47:24

quote:
Originally posted by losstww

Tara,

Thanks for the lead. I settled on:

SELECT A1.EmployeeID, SUM(ISNULL(A2.Amount, 0.00)) AS Assets, SUM(ISNULL(A3.Recovery, 0.00)) AS Recovery
FROM dbo.Employees AS A1 LEFT OUTER JOIN
dbo.Assets AS A2 ON A1.EmployeeID = A2.EmployeeID LEFT OUTER JOIN
dbo.Recovery AS A3 ON A1.EmployeeID = A3.EmployeeID
GROUP BY A1.EmployeeID

A slow walk in the park on a sunny day can be very productive.

Thanks again,

losstww

Suggest you to go through the joins in books online to understand different types of joins and their differences in behaviour.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources