The field dolzhn_atest.KOD_C contains many lines, but there is only one match with the field kdpr.KDNSLP. As a result, one line is displayed, but you need to display everything. I can not understand the hour, I tried the left join, it did not help.

select kdpr.N_KDK, --табельный номер ltrim(rtrim(kdpr.fam))+' '+ltrim(rtrim(kdpr.nam))+ ' ' +ltrim(rtrim(kdpr.otch)) as FIO, --ФИО slp.NSLP, --Должность dolzhn_atest.NAIM as Doljnost_po_atest, -- Должн по асестац. kategor.NAIM as Kategor, --категория KDK.GR_INVA --инвалидность from kdpr inner join dbo.SLP on KDPR.KSLP = slp.KSLP inner join dbo.sp2 as dolzhn_atest on dolzhn_atest.KOD_C = kdpr.KDNSLP inner join dbo.sp2 as kategor on kategor.KOD_C = kdpr.KK36 inner join dbo.KDK on KDK.N_KDK = KDPR.N_KDK where dolzhn_atest.spr = 'dnslp' and kategor.SPR='k36'
  • @ Vladislav Sergeevich, If you are given an exhaustive answer, mark it as correct (click on the check mark next to the selected answer). - Donil

1 answer 1

where dolzhn_atest.spr = 'dnslp' and kategor.SPR='k36' - if this condition is fulfilled for only one line, then only one line will be displayed. If it is necessary that only entries with spr = 'dnslp' are selected from dolzhn_atest and it is connected with kpdr - then you need to write something like this:

 select kdpr.N_KDK, --табельный номер ltrim(rtrim(kdpr.fam))+' '+ltrim(rtrim(kdpr.nam))+ ' ' +ltrim(rtrim(kdpr.otch)) as FIO, --ФИО slp.NSLP, --Должность dolzhn_atest.NAIM as Doljnost_po_atest, -- Должн по асестац. kategor.NAIM as Kategor, --категория KDK.GR_INVA --инвалидность from kdpr inner join dbo.SLP on KDPR.KSLP = slp.KSLP left join dbo.sp2 as dolzhn_atest on dolzhn_atest.KOD_C = kdpr.KDNSLP and dolzhn_atest.spr = 'dnslp' inner join dbo.sp2 as kategor on kategor.KOD_C = kdpr.KK36 inner join dbo.KDK on KDK.N_KDK = KDPR.N_KDK where kategor.SPR='k36'
  • No, the result is the same - toris
  • added a couple of built-in delect and everything turned out - toris
  • sqlfiddle.com/#!6/a1188/2 - right? - Zufir
  • A little corrected, but in general, yes, thank you. how to close the question? - toris
  • Only here kategor.NAIM AS Kategor in your example returns NULL values, I had to correct, and ATP - toris