asked on

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

jimyX

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?

ASKER CERTIFIED SOLUTION

HainKurt

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

HainKurt

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

doramail05

ASKER

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

HainKurt

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

doramail05

ASKER

k, good,
close to the result,

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

gvoutput.jpg

HainKurt

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...

HainKurt

how do you query "up to Lesson05"????

doramail05

ASKER

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

doramail05

ASKER

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

Anthony Perkins

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.

Anthony Perkins

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

doramail05

ASKER

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

Anthony Perkins

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.