Showing posts with label Mysql Right Join. Show all posts
Showing posts with label Mysql Right Join. Show all posts

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?