YVS1102 YVS1102 - 1 month ago 17
SQL Question

Select Query Join select TOP 1

I have three table here:

DinasHoHeader
,
DinasHoDetail
and
Employee
.

First I have this Query

select a.KodeDinasHeader,c.Nip,DepartmentCode
from DinasHoHeader a
left join DinasHoDetail b on a.KodeDinasHeader = b.KodeDinasHeader
left join employee c on b.Nip = c.Nip
where DepartmentCode = 'IT'


and I get this result

KodeDinasHeader Nip DepartmentCode
DN0000007 1501971 IT
DN0000007 1411517 IT


As you can see i get duplicate
KodeDinasHeader
. I know it's because i have different
Nip
. Because i want to just show
KodeDinasHeader
and
DepartmentCode
only. I'm trying to change my query to this

SELECT a.KodeDinasHeader,DepartmentCode From DinasHoHeader a
left join (
select TOP 1 * from DinasHoDetail
) b on a.KodeDinasHeader = b.KodeDinasHeader
left join employee c on b.Nip = c.Nip
where c.DepartmentCode = 'IT'


I get empty result. How can i achieve this?

KodeDinasHeader DepartmentCode
DN0000007 IT

Answer

try this:

select DISTINCT a.KodeDinasHeader,DepartmentCode 
from DinasHoHeader a 
left join DinasHoDetail b on a.KodeDinasHeader = b.KodeDinasHeader 
left join employee c on b.Nip = c.Nip
where DepartmentCode = 'IT'

Note, I've added DISTINCT and removed the Nip column from the select list.

Comments