Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 255
  • Last Modified:

LEFT JOIN or select all records inspite of null on the right columns

trying to display grid like

Title01        Title02      Title03
-------          -------       -------
data011      data021    data031
data022      data022    data032
data033
data044
data055

inspite of data023, data033 , data024, data034 , etc are NULL or empty.
SELECT 
 
l.lessonno as lessoncode,
sa.actualtime as actualtime,
sa.attendancestatus as attstatus,
sa.lateduration as lateduration,
(
select sa1.actualtime from traineemodule tm 
JOIN StudentAttendance sa1 ON tm.said = sa1.id
JOIN ClassType ct ON tm.ctid = ct.id
JOIN Trainer tr ON tm.trid = tr.id
JOIN Student st ON tm.sid = st.id
JOIN Period p ON tm.pid = p.id
JOIN Lesson l ON tm.lid = l.id
LEFT JOIN Module m ON tm.mid = m.id
WHERE st.id = 1 AND p.intakename = @intake AND l.lessonname='lesson13' AND ct.classtype='LAB'
AND m.modulename = @module
) as lab,
(
select sa1.actualtime from traineemodule tm 
JOIN StudentAttendance sa1 ON tm.said = sa1.id
JOIN ClassType ct ON tm.ctid = ct.id
JOIN Trainer tr ON tm.trid = tr.id
JOIN Student st ON tm.sid = st.id
JOIN Period p ON tm.pid = p.id
JOIN Lesson l ON tm.lid = l.id
LEFT JOIN Module m ON tm.mid = m.id
WHERE st.id = 1 AND p.intakename = @intake AND l.lessonname='lesson13' AND ct.classtype='Evaluation'
AND m.modulename = @module
) as evaluation,
(
select sa1.actualtime from traineemodule tm 
JOIN StudentAttendance sa1 ON tm.said = sa1.id
JOIN ClassType ct ON tm.ctid = ct.id
JOIN Trainer tr ON tm.trid = tr.id
JOIN Student st ON tm.sid = st.id
JOIN Period p ON tm.pid = p.id
JOIN Lesson l ON tm.lid = l.id
LEFT JOIN Module m ON tm.mid = m.id
WHERE st.id = 1 AND p.intakename = @intake AND l.lessonname='lesson13' AND ct.classtype='Final Exam'
AND m.modulename = @module
) as finalexam 
FROM traineemodule tm 
JOIN StudentAttendance sa ON tm.said = sa.id
JOIN ClassType ct ON tm.ctid = ct.id
JOIN Trainer tr ON tm.trid = tr.id
JOIN Student st ON tm.sid = st.id
JOIN Period p ON tm.pid = p.id
JOIN Lesson l ON tm.lid = l.id
LEFT JOIN Module m ON tm.mid = m.id
WHERE st.id = 1 AND p.intakename = @intake AND l.lessonname='lesson13' AND ct.classtype='Quiz'
AND m.modulename = @module

Open in new window

0
doramail05
Asked:
doramail05
  • 5
  • 5
  • 3
  • +1
1 Solution
 
jimyXCommented:
You can use IsNull to replace the null values:
SELECT  
ISNULL(l.lessonno, 'Blank') as lessoncode,
ISNULL(sa.actualtime, 'Blank') as actualtime,
ISNULL(sa.attendancestatus, 'Blank') as attstatus,
ISNULL(sa.lateduration, 'Blank') as lateduration,....

Is that what you mean?
0
 
HainKurtSr. System AnalystCommented:
what do you get from this query
SELECT 
l.lessonno as lessoncode,
sa.actualtime as actualtime,
sa.attendancestatus as attstatus,
sa.lateduration as lateduration,
(case sa.actualtime when 'Lab' then sa.actualtime else null end) as lab
(case sa.actualtime when 'Quiz' then sa.actualtime else null end) as Quiz
(case sa.actualtime when 'Evaluation' then sa.actualtime else null end) as Evaluation
(case sa.actualtime when 'Final Exam' then sa.actualtime else null end) as FinalExam
FROM traineemodule tm 
JOIN StudentAttendance sa ON tm.said = sa.id
JOIN ClassType ct ON tm.ctid = ct.id
JOIN Trainer tr ON tm.trid = tr.id
JOIN Student st ON tm.sid = st.id
JOIN Period p ON tm.pid = p.id
JOIN Lesson l ON tm.lid = l.id
LEFT JOIN Module m ON tm.mid = m.id
WHERE st.id = 1 AND p.intakename = @intake AND l.lessonname='lesson13' AND ct.classtype in ('Quiz','LAB','Evaluation', 'Final Exam')
AND m.modulename = @module

Open in new window

0
 
HainKurtSr. System AnalystCommented:
and wrapping it with another group select
select lessoncode, attstatus, lateduration, max(lab) as lab, max(quiz) as quiz, max(Evaluation) as Evaluation, max(FinalExam) as FinalExam
from (
	SELECT 
	l.lessonno as lessoncode,
	sa.attendancestatus as attstatus,
	sa.lateduration as lateduration,
	(case sa.actualtime when 'Lab' then sa.actualtime else null end) as lab
	(case sa.actualtime when 'Quiz' then sa.actualtime else null end) as Quiz
	(case sa.actualtime when 'Evaluation' then sa.actualtime else null end) as Evaluation
	(case sa.actualtime when 'Final Exam' then sa.actualtime else null end) as FinalExam
	FROM traineemodule tm 
	JOIN StudentAttendance sa ON tm.said = sa.id
	JOIN ClassType ct ON tm.ctid = ct.id
	JOIN Trainer tr ON tm.trid = tr.id
	JOIN Student st ON tm.sid = st.id
	JOIN Period p ON tm.pid = p.id
	JOIN Lesson l ON tm.lid = l.id
	LEFT JOIN Module m ON tm.mid = m.id
	WHERE st.id = 1 AND p.intakename = @intake AND l.lessonname='lesson13' AND ct.classtype in ('Quiz','LAB','Evaluation', 'Final Exam')
	AND m.modulename = @module
) x group by lessoncode, attstatus, lateduration

Open in new window

0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
doramail05Author Commented:
jimyX:
grid has no data  o.O

HainKurt:
attstatus, lab, quiz, sa.attendancestatus , etc
have errors

the objective is to get something like left join , since lesson on the left will always have data, but quiz and lab might have null value,
and the grid has to display everything from L01-L05 inspite of the empty data quiz and lab.

Lesson      Quiz            Lab
--------       ------            -----
L01           12/12/2011  11/12/2011
L02           12/11/2011   11/11/2011
L03
L04
L05

0
 
HainKurtSr. System AnalystCommented:
oops, sorry... some missing "," and a column change in case statements... try this again...
select lessoncode, attstatus, lateduration, max(lab) as lab, max(quiz) as quiz, max(Evaluation) as Evaluation, max(FinalExam) as FinalExam
from (
	SELECT 
	l.lessonno as lessoncode,
	sa.attendancestatus as attstatus,
	sa.lateduration as lateduration,
	(case ct.classtype when 'Lab' then sa.actualtime else null end) as lab,
	(case ct.classtype when 'Quiz' then sa.actualtime else null end) as Quiz,
	(case ct.classtype when 'Evaluation' then sa.actualtime else null end) as Evaluation,
	(case ct.classtype when 'Final Exam' then sa.actualtime else null end) as FinalExam
	FROM traineemodule tm 
	JOIN StudentAttendance sa ON tm.said = sa.id
	JOIN ClassType ct ON tm.ctid = ct.id
	JOIN Trainer tr ON tm.trid = tr.id
	JOIN Student st ON tm.sid = st.id
	JOIN Period p ON tm.pid = p.id
	JOIN Lesson l ON tm.lid = l.id
	LEFT JOIN Module m ON tm.mid = m.id
	WHERE st.id = 1 AND p.intakename = @intake AND l.lessonname='lesson13' AND ct.classtype in ('Quiz','LAB','Evaluation', 'Final Exam')
	AND m.modulename = @module
) x group by lessoncode, attstatus, lateduration

Open in new window

0
 
doramail05Author Commented:
k, good,
close to the result,

but when i query up to Lesson05
it only produce results up to Lesson02 only,

gvoutput.jpg
0
 
HainKurtSr. System AnalystCommented:
if thats the case you are missing some data in your tables...
maybe you need to convert all joins to left joins... the image you posted is from inner select query and if you use wrapper sql around this you will get what you want... but you need to solve the data issue first... check your tables to find why the rest is missing...
0
 
HainKurtSr. System AnalystCommented:
how do you query "up to Lesson05"????
0
 
doramail05Author Commented:
like this,,

by right it should show :
L1      12/12/2011         11/12/2011
L2      11/11/2011         10/11/2011
L3      
L4
L5
select lessoncode, attstatus, lateduration, max(lab) as lab, max(quiz) as quiz, max(Evaluation) as Evaluation, max(FinalExam) as FinalExam
from (
	SELECT 
	l.lessonno as lessoncode,
	sa.attendancestatus as attstatus,
	sa.lateduration as lateduration,
	(case ct.classtype when 'Lab' then sa.actualtime else null end) as lab,
	(case ct.classtype when 'Quiz' then sa.actualtime else null end) as Quiz,
	(case ct.classtype when 'Evaluation' then sa.actualtime else null end) as Evaluation,
	(case ct.classtype when 'Final Exam' then sa.actualtime else null end) as FinalExam
	FROM traineemodule tm 
	LEFT JOIN StudentAttendance sa ON tm.said = sa.id
	LEFT JOIN ClassType ct ON tm.ctid = ct.id
	LEFT JOIN Trainer tr ON tm.trid = tr.id
	LEFT JOIN Student st ON tm.sid = st.id
	LEFT JOIN Period p ON tm.pid = p.id
	LEFT JOIN Lesson l ON tm.lid = l.id
	LEFT JOIN  Module m ON tm.mid = m.id
	WHERE st.id = 1 AND p.intakename = 'Dec2011' AND l.lessonname='lesson01' AND ct.classtype in ('Quiz','LAB','Evaluation', 'Final Exam')
	AND m.modulename = 'module01'
) x group by lessoncode, attstatus, lateduration

UNION
select lessoncode, attstatus, lateduration, max(lab) as lab, max(quiz) as quiz, max(Evaluation) as Evaluation, max(FinalExam) as FinalExam
from (
	SELECT 
	l.lessonno as lessoncode,
	sa.attendancestatus as attstatus,
	sa.lateduration as lateduration,
	(case ct.classtype when 'Lab' then sa.actualtime else null end) as lab,
	(case ct.classtype when 'Quiz' then sa.actualtime else null end) as Quiz,
	(case ct.classtype when 'Evaluation' then sa.actualtime else null end) as Evaluation,
	(case ct.classtype when 'Final Exam' then sa.actualtime else null end) as FinalExam
	FROM traineemodule tm 
	LEFT JOIN StudentAttendance sa ON tm.said = sa.id
	LEFT JOIN ClassType ct ON tm.ctid = ct.id
	LEFT JOIN Trainer tr ON tm.trid = tr.id
	LEFT JOIN Student st ON tm.sid = st.id
	LEFT JOIN Period p ON tm.pid = p.id
	LEFT JOIN Lesson l ON tm.lid = l.id
	LEFT JOIN  Module m ON tm.mid = m.id
	WHERE st.id = 1 AND p.intakename = 'Dec2011' AND l.lessonname='lesson02' AND ct.classtype in ('Quiz','LAB','Evaluation', 'Final Exam')
	AND m.modulename = 'module01'
) x group by lessoncode, attstatus, lateduration


UNION
select lessoncode, attstatus, lateduration, max(lab) as lab, max(quiz) as quiz, max(Evaluation) as Evaluation, max(FinalExam) as FinalExam
from (
	SELECT 
	l.lessonno as lessoncode,
	sa.attendancestatus as attstatus,
	sa.lateduration as lateduration,
	(case ct.classtype when 'Lab' then sa.actualtime else null end) as lab,
	(case ct.classtype when 'Quiz' then sa.actualtime else null end) as Quiz,
	(case ct.classtype when 'Evaluation' then sa.actualtime else null end) as Evaluation,
	(case ct.classtype when 'Final Exam' then sa.actualtime else null end) as FinalExam
	FROM traineemodule tm 
	LEFT JOIN StudentAttendance sa ON tm.said = sa.id
	LEFT JOIN ClassType ct ON tm.ctid = ct.id
	LEFT JOIN Trainer tr ON tm.trid = tr.id
	LEFT JOIN Student st ON tm.sid = st.id
	LEFT JOIN Period p ON tm.pid = p.id
	LEFT JOIN Lesson l ON tm.lid = l.id
	LEFT JOIN  Module m ON tm.mid = m.id
	WHERE st.id = 1 AND p.intakename = 'Dec2011' AND l.lessonname='lesson03' AND ct.classtype in ('Quiz','LAB','Evaluation', 'Final Exam')
	AND m.modulename = 'module01'
) x group by lessoncode, attstatus, lateduration

UNION
select lessoncode, attstatus, lateduration, max(lab) as lab, max(quiz) as quiz, max(Evaluation) as Evaluation, max(FinalExam) as FinalExam
from (
	SELECT 
	l.lessonno as lessoncode,
	sa.attendancestatus as attstatus,
	sa.lateduration as lateduration,
	(case ct.classtype when 'Lab' then sa.actualtime else null end) as lab,
	(case ct.classtype when 'Quiz' then sa.actualtime else null end) as Quiz,
	(case ct.classtype when 'Evaluation' then sa.actualtime else null end) as Evaluation,
	(case ct.classtype when 'Final Exam' then sa.actualtime else null end) as FinalExam
	FROM traineemodule tm 
	LEFT JOIN StudentAttendance sa ON tm.said = sa.id
	LEFT JOIN ClassType ct ON tm.ctid = ct.id
	LEFT JOIN Trainer tr ON tm.trid = tr.id
	LEFT JOIN Student st ON tm.sid = st.id
	LEFT JOIN Period p ON tm.pid = p.id
	LEFT JOIN Lesson l ON tm.lid = l.id
	LEFT JOIN  Module m ON tm.mid = m.id
	WHERE st.id = 1 AND p.intakename = 'Dec2011' AND l.lessonname='lesson04' AND ct.classtype in ('Quiz','LAB','Evaluation', 'Final Exam')
	AND m.modulename = 'module01'
) x group by lessoncode, attstatus, lateduration
UNION
select lessoncode, attstatus, lateduration, max(lab) as lab, max(quiz) as quiz, max(Evaluation) as Evaluation, max(FinalExam) as FinalExam
from (
	SELECT 
	l.lessonno as lessoncode,
	sa.attendancestatus as attstatus,
	sa.lateduration as lateduration,
	(case ct.classtype when 'Lab' then sa.actualtime else null end) as lab,
	(case ct.classtype when 'Quiz' then sa.actualtime else null end) as Quiz,
	(case ct.classtype when 'Evaluation' then sa.actualtime else null end) as Evaluation,
	(case ct.classtype when 'Final Exam' then sa.actualtime else null end) as FinalExam
	FROM traineemodule tm 
	LEFT JOIN StudentAttendance sa ON tm.said = sa.id
	LEFT JOIN ClassType ct ON tm.ctid = ct.id
	LEFT JOIN Trainer tr ON tm.trid = tr.id
	LEFT JOIN Student st ON tm.sid = st.id
	LEFT JOIN Period p ON tm.pid = p.id
	LEFT JOIN Lesson l ON tm.lid = l.id
	LEFT JOIN  Module m ON tm.mid = m.id
	WHERE st.id = 1 AND p.intakename = 'Dec2011' AND l.lessonname='lesson05' AND ct.classtype in ('Quiz','LAB','Evaluation', 'Final Exam')
	AND m.modulename = 'module01'
) x group by lessoncode, attstatus, lateduration

Open in new window

0
 
doramail05Author Commented:
is there any chance it could show something like

lessoncode, attstatus, max(lab) as lab, max(quiz) as quiz, max(Evaluation) as Evaluation, max(FinalExam) as FinalExam, attstatuslab, attstatusquiz, attstatuseval, attstatusfinal

total of 10 columns,

because each quiz, eval, lab has its own attstatus, from
 
select lessoncode, attstatus, max(lab) as lab, max(quiz) as quiz, max(Evaluation) as Evaluation, max(FinalExam) as FinalExam, attstatuslab, attstatusquiz, attstatuseval, attstatusfinal
from (
	SELECT 
	l.lessonno as lessoncode,
	sa.attendancestatus as attstatus,
	sa.lateduration as lateduration,
	(case ct.classtype when 'Lab' then sa.actualtime else null end) as lab,
	(case ct.classtype when 'Quiz' then sa.actualtime else null end) as Quiz,
	(case ct.classtype when 'Evaluation' then sa.actualtime else null end) as Evaluation,
	(case ct.classtype when 'Final Exam' then sa.actualtime else null end) as FinalExam,
	(select sa.attendancestatus from StudentAttendance sa WHERE ct.classtype = 'lab') as attstatuslab,
	(select sa.attendancestatus from StudentAttendance sa WHERE ct.classtype = 'Quiz') as attstatusquiz,
	(select sa.attendancestatus from StudentAttendance sa WHERE ct.classtype = 'Evaluation') as attstatuseval,
	(select sa.attendancestatus from StudentAttendance sa WHERE ct.classtype = 'Final Exam') as attstatusfinal
	FROM traineemodule tm 
	LEFT JOIN StudentAttendance sa ON tm.said = sa.id
	LEFT JOIN ClassType ct ON tm.ctid = ct.id
	LEFT JOIN Trainer tr ON tm.trid = tr.id
	LEFT JOIN Student st ON tm.sid = st.id
	LEFT JOIN Period p ON tm.pid = p.id
	LEFT JOIN Lesson l ON tm.lid = l.id
	LEFT JOIN  Module m ON tm.mid = m.id
	WHERE st.id = 1 AND p.intakename = 'Dec2011' AND l.lessonname='lesson01' AND ct.classtype in ('Quiz','LAB','Evaluation', 'Final Exam')
	AND m.modulename = 'module01'
) x group by lessoncode, attstatus, lateduration

Open in new window

0
 
Anthony PerkinsCommented:
Here is the problem:  You think you are doing a LEFT JOIN when in fact you are doing an INNER JOIN.  This:
FROM traineemodule tm
LEFT JOIN StudentAttendance sa ON tm.said = sa.id
LEFT JOIN ClassType ct ON tm.ctid = ct.id
LEFT JOIN Trainer tr ON tm.trid = tr.id
LEFT JOIN Student st ON tm.sid = st.id
LEFT JOIN Period p ON tm.pid = p.id
LEFT JOIN Lesson l ON tm.lid = l.id
LEFT JOIN  Module m ON tm.mid = m.id
WHERE st.id = 1 AND p.intakename = 'Dec2011' AND l.lessonname='lesson01' AND ct.classtype in ('Quiz','LAB','Evaluation', 'Final Exam')
AND m.modulename = 'module01'

Is precisely the same as this:
FROM traineemodule tm
LEFT JOIN StudentAttendance sa ON tm.said = sa.id
INNER JOIN ClassType ct ON tm.ctid = ct.id
LEFT JOIN Trainer tr ON tm.trid = tr.id
INNER JOIN Student st ON tm.sid = st.id
INNER JOIN Period p ON tm.pid = p.id
INNER JOIN Lesson l ON tm.lid = l.id
INNER JOIN  Module m ON tm.mid = m.id
WHERE st.id = 1 AND p.intakename = 'Dec2011' AND l.lessonname='lesson01' AND ct.classtype in ('Quiz','LAB','Evaluation', 'Final Exam')
AND m.modulename = 'module01'

But don't take my word for it, try it out.
0
 
Anthony PerkinsCommented:
If you really want a LEFT JOIN than you should be doing it this way:
FROM	traineemodule tm
	LEFT JOIN StudentAttendance sa ON tm.said = sa.id
	LEFT JOIN ClassType ct ON tm.ctid = ct.id AND ct.classtype IN ('Quiz', 'LAB', 'Evaluation', 'Final Exam')
	LEFT JOIN Trainer tr ON tm.trid = tr.id
	LEFT JOIN Student st ON tm.sid = st.id AND st.id = 1
	LEFT JOIN Period p ON tm.pid = p.id AND p.intakename = 'Dec2011'
	LEFT JOIN Lesson l ON tm.lid = l.id AND l.lessonname = 'lesson01'
	LEFT JOIN Module m ON tm.mid = m.id AND m.modulename = 'module01'

Open in new window

0
 
doramail05Author Commented:
finally got it somehow
SELECT 
 
l.lessonno as lessoncode,
sa.actualtime as actualtime,
sa.attendancestatus as attstatus,
sa.lateduration as lateduration,
(
select sa1.actualtime from traineemodule tm 
JOIN StudentAttendance sa1 ON tm.said = sa1.id
JOIN ClassType ct ON tm.ctid = ct.id
JOIN Trainer tr ON tm.trid = tr.id
JOIN Student st ON tm.sid = st.id
JOIN Period p ON tm.pid = p.id
JOIN Lesson l ON tm.lid = l.id
LEFT JOIN Module m ON tm.mid = m.id
WHERE st.id = @studentid AND p.intakename = @intake AND l.lessonname='lesson01' AND ct.classtype='LAB'
AND m.modulename = @module
) as lab,
(
select sa1.actualtime from traineemodule tm 
JOIN StudentAttendance sa1 ON tm.said = sa1.id
JOIN ClassType ct ON tm.ctid = ct.id
JOIN Trainer tr ON tm.trid = tr.id
JOIN Student st ON tm.sid = st.id
JOIN Period p ON tm.pid = p.id
JOIN Lesson l ON tm.lid = l.id
LEFT JOIN Module m ON tm.mid = m.id
WHERE st.id = @studentid AND p.intakename = @intake AND l.lessonname='lesson01' AND ct.classtype='Evaluation'
AND m.modulename = @module
) as evaluation,
(
select sa1.actualtime from traineemodule tm 
JOIN StudentAttendance sa1 ON tm.said = sa1.id
JOIN ClassType ct ON tm.ctid = ct.id
JOIN Trainer tr ON tm.trid = tr.id
JOIN Student st ON tm.sid = st.id
JOIN Period p ON tm.pid = p.id
JOIN Lesson l ON tm.lid = l.id
LEFT JOIN Module m ON tm.mid = m.id
WHERE st.id = @studentid AND p.intakename = @intake AND l.lessonname='lesson01' AND ct.classtype='Final Exam'
AND m.modulename = @module
) as finalexam,
(
select sa1.attendancestatus from traineemodule tm 
JOIN StudentAttendance sa1 ON tm.said = sa1.id
JOIN ClassType ct ON tm.ctid = ct.id
JOIN Trainer tr ON tm.trid = tr.id
JOIN Student st ON tm.sid = st.id
JOIN Period p ON tm.pid = p.id
JOIN Lesson l ON tm.lid = l.id
LEFT JOIN Module m ON tm.mid = m.id
WHERE st.id = @studentid AND p.intakename = @intake AND l.lessonname='lesson01' AND ct.classtype='Quiz'
AND m.modulename = @module
) as attstatusQuiz,
(
select sa1.attendancestatus from traineemodule tm 
JOIN StudentAttendance sa1 ON tm.said = sa1.id
JOIN ClassType ct ON tm.ctid = ct.id
JOIN Trainer tr ON tm.trid = tr.id
JOIN Student st ON tm.sid = st.id
JOIN Period p ON tm.pid = p.id
JOIN Lesson l ON tm.lid = l.id
LEFT JOIN Module m ON tm.mid = m.id
WHERE st.id = @studentid AND p.intakename = @intake AND l.lessonname='lesson01' AND ct.classtype='LAB'
AND m.modulename = @module
) as attstatuslab,
(
select sa1.attendancestatus from traineemodule tm 
JOIN StudentAttendance sa1 ON tm.said = sa1.id
JOIN ClassType ct ON tm.ctid = ct.id
JOIN Trainer tr ON tm.trid = tr.id
JOIN Student st ON tm.sid = st.id
JOIN Period p ON tm.pid = p.id
JOIN Lesson l ON tm.lid = l.id
LEFT JOIN Module m ON tm.mid = m.id
WHERE st.id = @studentid AND p.intakename = @intake AND l.lessonname='lesson01' AND ct.classtype='Evaluation'
AND m.modulename = @module
) as attstatuseval,
(
select sa1.attendancestatus from traineemodule tm 
JOIN StudentAttendance sa1 ON tm.said = sa1.id
JOIN ClassType ct ON tm.ctid = ct.id
JOIN Trainer tr ON tm.trid = tr.id
JOIN Student st ON tm.sid = st.id
JOIN Period p ON tm.pid = p.id
JOIN Lesson l ON tm.lid = l.id
LEFT JOIN Module m ON tm.mid = m.id
WHERE st.id = @studentid AND p.intakename = @intake AND l.lessonname='lesson01' AND ct.classtype='Final Exam'
AND m.modulename = @module
) as attstatusfinalexam
  
FROM traineemodule tm 
JOIN StudentAttendance sa ON tm.said = sa.id
JOIN ClassType ct ON tm.ctid = ct.id
JOIN Trainer tr ON tm.trid = tr.id
JOIN Student st ON tm.sid = st.id
JOIN Period p ON tm.pid = p.id
JOIN Lesson l ON tm.lid = l.id
LEFT JOIN Module m ON tm.mid = m.id
WHERE st.id = @studentid AND p.intakename = @intake AND l.lessonname='lesson01' AND ct.classtype='Quiz'
AND m.modulename = @module

UNION

..

Open in new window

0
 
Anthony PerkinsCommented:
Unfortunately, you do not seem to realize how LEFT OUTER JOINS operate.
This:
LEFT JOIN Module m ON tm.mid = m.id
WHERE st.id = @studentid AND p.intakename = @intake AND l.lessonname='lesson01' AND ct.classtype='Quiz'
AND m.modulename = @module

In your case is exactly the same as this:
INNER JOIN Module m ON tm.mid = m.id
WHERE st.id = @studentid AND p.intakename = @intake AND l.lessonname='lesson01' AND ct.classtype='Quiz'
AND m.modulename = @module

If you really want a LEFT JOIN than you need to write it this way:
LEFT JOIN Module m ON tm.mid = m.id AND m.modulename = @module
WHERE st.id = @studentid AND p.intakename = @intake AND l.lessonname='lesson01' AND ct.classtype='Quiz'

But don't take my word for it, test it out and see for yourself.
0

Featured Post

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

  • 5
  • 5
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now