Tuesday, 28 August 2018

Rright join that does not give null values

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