Merge results from 2 Select with no different fields

Asked

Viewed 36 times

-1

Good morning,

I have a question about creating a query where the second select has only one field and this field has to appear in the first select.

the relationship data between the tables are: CODFORNECEDOR, CODFILIAL, CODPRODUTO

Note that in the image below the first select has several fields and the second only 4 fields of this 4 fields I need only the first LAST ENTRY that is related to the last item of the first select

follow the select if anyone can help me, thank you.

FIRST SELECT

DECLARE @cNroFilial  VARCHAR(2)
DECLARE @cNroProtoc  VARCHAR(6)

SET @cNroFilial ='51'
SET @cNroProtoc ='194409'

SELECT
    SUD.UD_FILORI   AS COD_FILIAL
    ,SUD.UD_ENTCLI  AS DTA_ENTCLIENTE
    ,SUD.UD_NFCLI   AS NTF_CLIENTE
    ,SUD.UD_SERCLI  AS NRO_SERIE
    ,SUD.UD_NFGAR   AS NFS_GARANTIA 
    ,SUD.UD_NUMGAR  AS NRO_GARANTIA
    ,SUD.UD_CODIGO  AS NRO_PROTOCOLO
    ,SUD.UD_FORN    AS COD_FORNECEDOR
    ,SUD.UD_PRODUTO AS COD_PRODUTO
    ,SUC.UC_CLIENTE AS COD_CLIENTE
    ,SD2.D2_CLIENTE AS COD_CLIENTE_SAIDA
    ,SD2.D2_LOJA    AS LOJ_CLIENTE_SAIDA
    ,SD2.D2_DOC     AS NOTAFISCAL_CLIENTE_SAIDA
    ,SD2.D2_EMISSAO AS DTA_SAIDA_CLIENTE

FROM 
    SUD010 SUD  WITH (NOLOCK) 
LEFT OUTER JOIN
    SUC010 SUC WITH (NOLOCK) ON
    SUC.D_E_L_E_T_    = ''
    AND SUC.UC_CODIGO = SUD.UD_CODIGO
    AND SUC.UC_FILORI = SUD.UD_FILORI   
    AND SUC.UC_DATA   = SUD.UD_DATA     
LEFT OUTER JOIN
    SD2010 SD2 WITH (NOLOCK) ON
    SD2.D_E_L_E_T_     = ''
    AND SD2.D2_COD     = SUD.UD_PRODUTO
    AND SD2.D2_FILIAL  = SUD.UD_FILORI
    AND SD2.D2_CLIENTE = SUC.UC_CLIENTE
    AND SD2.D2_LOJA    = SUC.UC_LOJA
    AND SD2.D2_DOC     = SUD.UD_NFGAR 
WHERE
    EXISTS
    ( SELECT TOP 1 MAX(D1_EMISSAO) ,D1_COD,D1_FORNECE
      FROM SD1010 WHERE
      D_E_L_E_T_='' 
      AND D1_FORNECE='000055' 
      AND D1_FILIAL='51' 
      AND D1_COD='3000001240'
        GROUP BY D1_COD,D1_FORNECE )
     -- AND SUD.UD_PRODUTO = SD1.D1_COD
     -- AND SUD.UD_FORN = SD1.D1_FORNECE 


    AND SUD.D_E_L_E_T_ =''
    AND SUD.UD_FILORI  = @cNroFilial
    AND SUD.UD_CODIGO  = @cNroProtoc
    AND SUC.UC_CODIGO  = @cNroProtoc

SECOND SELECT

SELECT TOP 1 MAX(D1_EMISSAO) AS ULTIMA_ENTRADA ,D1_FILIAL,D1_COD,D1_FORNECE
FROM SD1010 WHERE D_E_L_E_T_='' 
AND D1_FORNECE='000055' 
AND D1_FILIAL='51' 
AND D1_COD='3000001240'
GROUP BY D1_FILIAL,D1_COD,D1_FORNECE

inserir a descrição da imagem aqui

sql-server

1 answer

0

all right?

What you can do is an INNER JOIN by the column D1_FILIAL with COD_FILIAL, you will get your desired result.


SELECT  MAX(SD.D1_EMISSAO) AS ULTIMA_ENTRADA

        ,SUD.UD_FILORI AS COD_FILIAL
        ,SUD.UD_ENTCLI AS DTA_ENTCLIENTE
        ,SUD.UD_NFCLI AS NTF_CLIENTE
        ,SUD.UD_SERCLI AS NRO_SERIE
        ,SUD.UD_NFGAR AS NFS_GARANTIA
        ,SUD.UD_NUMGAR AS NRO_GARANTIA
        ,SUD.UD_CODIGO AS NRO_PROTOCOLO
        ,SUD.UD_FORN AS COD_FORNECEDOR
        ,SUD.UD_PRODUTO AS COD_PRODUTO
        ,SUC.UC_CLIENTE AS COD_CLIENTE
        ,SD2.D2_CLIENTE AS COD_CLIENTE_SAIDA
        ,SD2.D2_LOJA AS LOJ_CLIENTE_SAIDA
        ,SD2.D2_DOC AS NOTAFISCAL_CLIENTE_SAIDA
        ,SD2.D2_EMISSAO AS DTA_SAIDA_CLIENTE

FROM    SUD010 SUD WITH (NOLOCK)
        LEFT OUTER JOIN SUC010 SUC WITH (NOLOCK) ON SUC.D_E_L_E_T_ = '' AND SUC.UC_CODIGO = SUD.UD_CODIGO AND SUC.UC_FILORI = SUD.UD_FILORI AND SUC.UC_DATA = SUD.UD_DATA
        LEFT OUTER JOIN SD2010 SD2 WITH (NOLOCK) ON SD2.D_E_L_E_T_ = '' AND SD2.D2_COD = SUD.UD_PRODUTO AND SD2.D2_FILIAL = SUD.UD_FILORI AND SD2.D2_CLIENTE = SUC.UC_CLIENTE AND SD2.D2_LOJA = SUC.UC_LOJA AND SD2.D2_DOC = SUD.UD_NFGAR
        INNER JOIN SD1010 SD WITH (NOLOCK) ON SD.D1_FILIAL = SUD.UD_FILORI AND SD.D1_COD = SUD.UD_FORN AND SD.D1_FORNECE = SUD.UD_PRODUTO AND SD.D_E_L_E_T_ = ''

WHERE   EXISTS ( SELECT TOP 1 MAX(D1_EMISSAO) ,D1_COD,D1_FORNECE FROM SD1010 WHERE D_E_L_E_T_='' AND D1_FORNECE='000055' AND D1_FILIAL='51' AND D1_COD='3000001240' GROUP BY D1_COD,D1_FORNECE ) -- AND SUD.UD_PRODUTO = SD1.D1_COD -- AND SUD.UD_FORN = SD1.D1_FORNECE

GROUP   BY SUD.UD_FILORI, SUD.UD_ENTCLI, SUD.UD_NFCLI, SUD.UD_SERCLI, SUD.UD_NFGAR, SUD.UD_NUMGAR, SUD.UD_CODIGO, SUD.UD_FORN, SUD.UD_PRODUTO, SUC.UC_CLIENTE, SD2.D2_CLIENTE
        , SD2.D2_LOJA, SD2.D2_DOC, SD2.D2_EMISSAO

Another option would be to make a VIEW for each of your SELECT, and then in another SELECT do the INNER JOIN das Views (as I did here direct).

(I’m guessing there’s a FOREIGN KEY to the columns in INNER JOIN).

Basically that’s it.

Hugs!

Browser other questions tagged sql-server

You are not signed in. Login or sign up in order to post.