Hi All.I generally work with Oracle and therefore are quite confused by the standard SQL join syntax.I have the following query: SELECT DISTINCT SUBSTRING(mod_reference,1,3) AS dept_code, SUM(CASE dstum_mark WHEN '/' THEN 1 WHEN 'L' THEN 1 WHEN 'P' THEN 1 WHEN 'E' THEN 1 WHEN 'A' THEN 1 WHEN '' THEN 1 WHEN '?' THEN 1 ELSE 0 END) as present, SUM(CASE dstum_mark WHEN 'O' THEN 1 WHEN 'S' THEN 1 ELSE 0 END) as absent, SUM(ISNULL(CASE dstum_mark WHEN '' THEN 1 ELSE 0 END,0)) as nomark, SUM(ISNULL(CASE dstum_mark WHEN '?' THEN 1 ELSE 0 END,0)) as investigate, SUM(CASE dstum_mark WHEN '/' THEN 1 WHEN 'O' THEN 1 WHEN 'A' THEN 1 WHEN 'L' THEN 1 WHEN 'P' THEN 1 WHEN 'E' THEN 1 WHEN 'S' THEN 1 ELSE 0 END) as possible, 100* SUM(CASE dstum_mark WHEN '/' THEN 1 WHEN 'L' THEN 1 WHEN 'P' THEN 1 WHEN 'E' THEN 1 WHEN 'A' THEN 1 ELSE 0 END)/ SUM(CASE dstum_mark WHEN '/' THEN 1 WHEN 'O' THEN 1 WHEN 'A' THEN 1 WHEN 'L' THEN 1 WHEN 'P' THEN 1 WHEN 'E' THEN 1 WHEN 'S' THEN 1 ELSE 0 END) as attendance FROM dstummark,Activity, Amr, Module, Arr, Room, Student WHERE Activity.Act_Syskey = Amr.Amr_Actkey AND Amr.Amr_Modkey = Module.Mod_Syskey AND dStummark.dStum_Actkey = Activity.Act_Syskey AND amr_actkey = arr_actkey AND arr_roomkey = room_syskey AND dstummark.dstum_markdate >= getdate()-7 AND stu_syskey = dstum_stukey AND CONVERT(int, DateDiff(m, stu_dob, GetDate()))/12 BETWEEN 19 AND 100AND room_site LIKE '%%' AND SUBSTRING(mod_reference,1,3) = 'AGK' GROUP BY SUBSTRING(mod_reference,1,3)
I have a list of valid department codes which is iterated through in .Net to trigger this query for every dept code. If the given dept code contains no matching records, nothing is returned. SUBSTRING(mod_reference,1,3) is a department code. I need the query to return either empty fields or fields containing zeroes if there are no matching records. I'm hoping a join in the query can be used to do this. In Oracle, I'd just use (+) but I'm completely stuck with how to use JOIN in SQL Server. My main confusion is that if the JOIN follows the syntax: FROM table1 RIGHT JOIN table 2 ON table1.key = table2.keywhat happens to any further tables you need in the from statement. And what if you need to join onto more than one field in each table. This is all so foreign to me so any help will be greatly appreciated.
Joins help required
Posted - 2008-02-07 : 07:06:29
