Got problem when Combines tables using left join

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

General SQL Server Forums

New to SQL Server Programming

Got problem when Combines tables using left join

Author

Topic

wkm1925
Posting Yak Master

207 Posts

Posted - 2007-03-27 : 05:00:00

Hi,

I'm having problem with left join ...

Let's say i've these following tables

academic
ID | year | level | m_l
----------------------------------------------
147 | 2006 | dip | 20
148 | 2006 | dip | 12

course
ID | year | level | course_desc | m_l
----------------------------------------------
147 | 2006 | dip | k | 7
147 | 2006 | dip | b | 11

Hint
----
(1) see course table, select sum(m_l) from course where ID=147 and year=2006 and level='dip' = 18
(2) after query from (1), if user select ID=147, year=2006, level=dip, then m_l receiving 2 or less only (0,1,2).

What i've done shown as follow:-

SELECT a.ID, a.Year, a.Level, a.m_l - b.m_l [value_left_in_course]
FROM
academic a
LEFT JOIN
(SELECT SUM(m_l) [m_l] FROM course
where ID=147 and year=2006 and level='dip'
GROUP BY ID) b
ON a.ID = b.ID
where a.ID=147 and a.year=2006 and a.level='dip'

the result is
ID | year | level | value_left_in_course
----------------------------------------------
147 | 2006 | dip | 2

MY PROBLEM IS WHEN NO ROWS in course when ID=148, then i'm query as above the result is
ID | year | level | value_left_in_course
----------------------------------------------
147 | 2006 | dip | (null)

How to writing SQL to changing (null), then the result shown as follow?
ID | year | level | value_left_in_course
----------------------------------------------
147 | 2006 | dip | 12

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-27 : 06:01:22

[code]
SELECT a.[ID], a.[year], a.[level], a.m_l - isnull(b.m_l, 0) [value_left_in_course]
FROM academic a
LEFT JOIN
(
SELECT [ID], SUM(m_l) as [m_l]
FROM course
where [ID] = 147
and [year] = 2006
and [level] = 'dip'
GROUP BY [ID]
) b
ON a.[ID] = b.[ID]
where a.[year] = 2006
and a.[level] = 'dip'
[/code]