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

wkm1925,
1st you need know on SELECT queries with WHERE clause you are filtering the row result of your query.
And for SELECT queries to filter an AGGREGATE FUNCTION you must filter it with HAVING clause.
SELECT a.ID As id, a.Year As yr, a.Level AS lev, SUM(a.m_l - b.m_l)
FROM academic a
LEFT OUTER JOIN course b
HAVING a.ID = 147
AND a.year = 2006
AND a.level ='dip'
GROUP BY id

HOPE IT WORKS.