QUERY SQL SERVER QUERY

Question

I have a question how to proceed in this case, where I have a query that would need the nserie was ignored or showed only the 1 item of that

SELECT
                ped.codfil,
                i.seqipd,
                i.codpro,
                p.codref,
                i.qtdped,
                i.unimed,
                i.usu_pesbar,
                i.codpro,
                isNull(d.numsep,0) nserie,
                i.codder tamanho,
                i.cplipd,
                i.obsipd,
                p.usu_preven,
                m.sigmoe [Moeda],
                p.usu_moepvs,
                FORMAT(i.preuni, 'N', 'pt-br') vlrunit,
                i.preuni,
                FORMAT(i.vlrliq, 'N', 'pt-br') vlrtotal,
                i.SitIpd,
                i.coddep,
                p.pesbru,
                i.usu_vlrfat,
                ped.usu_forcal,
                i.perdsc,
                i.usu_desapr,
                i.usu_vlrdsc,
                p.ctrsep
            FROM E120PED ped, E075PRO p, E031MOE m, E120IPD i
            LEFT OUTER JOIN E120DLS d
                ON (i.codemp=d.codemp
                    AND i.codfil=d.CodFil
                    AND i.numped=d.numped
                    AND i.seqipd=d.seqipd)
            WHERE ped.codemp=i.codemp
            AND ped.codfil=i.CodFil
            AND ped.numped=i.numped
            AND i.codmoe=m.codmoe
            AND i.codemp=p.codemp
            AND i.codpro=p.codpro
            AND i.codemp=10
            AND i.numped = '709'
            AND i.codfil = '501'

            group by 
            ped.codfil,
                i.seqipd,
                i.codpro,
                p.codref,
                i.qtdped,
                i.unimed,
                i.usu_pesbar,
                i.codpro,
                d.numsep,
                i.codder ,
                i.cplipd,
                i.obsipd,
                p.usu_preven,
                m.sigmoe,
                p.usu_moepvs,
                i.vlrliq,
                i.preuni,                   
                i.SitIpd,
                i.coddep,
                p.pesbru,
                i.usu_vlrfat,
                ped.usu_forcal,
                i.perdsc,
                i.usu_desapr,
                i.usu_vlrdsc,
                p.ctrsep

                ORDER BY i.seqipd DESC

Answer 1

Hello, when you say only 1 item of this, you mean the first result of the E120DLS table? If yes, you can instead of using a LEFT JOIN, make a subselect for the field.

EX:

SELECT
            ped.codfil,
            i.seqipd,
            i.codpro,
            p.codref,
            i.qtdped,
            i.unimed,
            i.usu_pesbar,
            i.codpro,
            (SELECT TOP 1 d.numsep FROM E120DLS WHERE ...) as nserie,