0

how to convert this left outer join query to subset query

select 
j.*, concat(d.Name, ', ', d.Gelar) as DSN, 
prg.Nama_Indonesia as PRG, 
kl.Kelas as kls, 
kl.Sesi as ssi 
from    
jadwal j left outer join 
dosen d on j.IDDosen=d.ID left outer join 
kelas kl on j.Kelas=kl.ID left outer join 
program prg on j.Program=prg.Kode left outer join 
jabatanorganisasi jo on d.JabatanOrganisasi=jo.Kode left outer join 
tahun t on j.tahun=t.id 
order by 
d.Name, prg.Nama_Indonesia, kl.Sesi, kl.Kelas;

please help give me axample

nahrun
  • 73
  • 1
  • 1
  • 6

2 Answers2

1

For the first query you will be having rows, since all of the joins are LEFT JOIN, so atleast all entries from table jadwal will be there in output.

But on the other query you are doing selection using conditions(works like INNER JOIN), if the condition not satisfies there wont be any result-set.Thats why you are not getting any outputs.

There is no data with these conditions.Please check data

where 
    j.IDDosen=d.ID and 
    j.kelas=kl.ID and 
    j.program=prg.kode and 
    j.tahun=t.id AND
    d.JabatanOrganisasi=jo.kode

Hope this helps

Subin Chalil
  • 3,531
  • 2
  • 24
  • 38
0

In the first query, you are using left joins (which means if there's no match between from table and the joining tables it would retrieve all the results from the from table), and in the second query you are using inner joins.

You can take a look on this What is the difference between "INNER JOIN" and "OUTER JOIN"?

Community
  • 1
  • 1
Frankely Diaz
  • 886
  • 1
  • 9
  • 16