I have two tables as:
select * from CallTypeDescription
select * from CallTypeDetails
I want to show all the records of idcalltype against its idjob.
Eg.
If idJob in
CallTypeDescription
is 96 and idCallType is 4 it should show records:
if idJob is 94 (i.e. does not exists in callType description) and idCallType is 4 then result should be:
Title Value idJob
test qu1 Null Null
test qu2 Null Null
For this i tried:
select a.Title,b.Value,b.idJob from CallTypeDescription b
right join CallTypeDetails a
on a.idCallType=b.idCallType
and a.idDetails=b.idCallTypeDetail
where a.idCallType=4 and b.idJob=96
But gives me result:
It should also add another row with test qu2 null null.
Plese help me.
Edit:
select a.Title,b.Value,b.idJob from CallTypeDescription b
right join CallTypeDetails a
on a.idCallType=b.idCallType
and a.idDetails=b.idCallTypeDetail
and a.idCallType=4
where b.idJob=96
It looks like you are after this effect (SQL Fiddle: http://sqlfiddle.com/#!3/8f98e/10):
select
a.Title,b.Value,b.idJob
from
(
select
IdDesc,
IdCallType,
IdJob,
IdCallTypeDetail,
Value
from
CallTypeDescription
where
IdJob = 96 --or 94
) b
right outer join
CallTypeDetails a
on
a.idCallType=b.idCallType
and
a.idDetails=b.idCallTypeDetail
where
a.idCallType=4
Is there a reason you have chosen to use a right join instead of a left one?
0 comments:
Post a Comment