Inner Join com select top 1

Question

Pessoal, boa tarde

Tenho a necessidade de montar um left join com o top 1 de determinada tabela, estava fazendo dessa forma, porém vi que está errado.

select *

from 
historicoCliente h   (Nolock)

left join 
    (
    SELECT TOP 1 jur.* FROM pessoa_log (Nolock)  
    left join pessoa p on p.cd_pessoa = jur.cd_pessoa
    where jur.status = 1 and jur.operacao = 'I'
    ORDER BY p.STATUS)
    logj ON (h.codempreedimento = logj.cd_pessoa_na and logj.status = 1 and logj.operacao = 'I')

Preciso selecionar o 1º registro de acordo com o join, da maneira que está, me retorna o primeiro registro da tabela de acordo com o where, alguém tem ideia de como fazer, estou utilizando o sql 2000.

Answer 1

Usando CTE talvez resolva seu problema

WITH CTE AS (SELECT jur.* FROM pessoa_log (nolock) left join pessoa p on p.cd_pessoa = jur.cd_pessoa where jur.status = 1 and jur.operacao = 'I')
select * from historicoCliente h (nolock) JOIN CTE on h.codempreendimento = CTE.cd_pessoa_na)

---

Se a sugestão resolver o problema, favor marcar como Resposta.

Answer 2

@Lucas, não tem CTE no SQL2000, infelizmente.

@Guilherme, eu não consegui enxergar quem é a tabela "jur". 

Poderia postar a estrutura correta do que esta fazendo?

[]´s

Answer 3

@Lucas, não tem CTE no SQL2000, infelizmente.

@Guilherme, eu não consegui enxergar quem é a tabela "jur". 

Poderia postar a estrutura correta do que esta fazendo?

[]´s

OMG agora que li que é em SQL 2000.

---

Se a sugestão resolver o problema, favor marcar como Resposta.

Answer 4

Advaldo, segue abaixo a query:

select * from historicoCliente h   (Nolock) 
left join
(
SELECT TOP 1 jur.* FROM pessoa_log jur (Nolock)
left join pessoa p on p.cd_pessoa = jur.cd_pessoa
where jur.status = 1 and jur.operacao = 'I'
ORDER BY p.STATUS)
logj ON (h.codempreedimento = logj.cd_pessoa_na and logj.status = 1 and logj.operacao = 'I')

Obrigado pela atenção.

Answer 5

O join que você fala é entre a historicoCliente com a logj correto?

Pq nao coloca este seu where fora, deste modo:

SELECT * FROM historicoCliente h (NOLOCK) 
LEFT JOIN
(
	SELECT jur.*, p.STATUS FROM pessoa_log jur (NOLOCK)
	LEFT join pessoa p (NOLOCK) ON p.cd_pessoa = jur.cd_pessoa
	-- WHERE jur.status = 1 AND jur.operacao = 'I'
	ORDER BY p.STATUS
)logj ON (h.codempreedimento = logj.cd_pessoa_na 
		AND logj.status = 1 
		AND logj.operacao = 'I')
WHERE logj.status = 1 AND logj.operacao = 'I'
order by logj.STATUS

Note que coloquei o where e o order by no final da sintaxe. Deste modo, você tem uma visão geral do histórico do cliente, selecionando o range que precisa.
Ao menos, foi o que entendi nesta sua consulta.
Veja se funcioan.

[]´s

Answer 6

Advaldo,

É esse join mesmo, o meu problema está em aplicar o TOP 1 no Left Join, o alias logj só pode retornar 1 linha pra cada linha de h.

Utilizando Outer Apply eu resolveria dessa forma:

SELECT * FROM históricoCliente					h   (Nolock)
outer apply 
(    
SELECT TOP 1 jur.*    
FROM pessoa_log jur    
left join pessoa p on p.cd_pessoa = jur.cd_pessoa    
where h.codcliente = jur.cd_pessoa_na    
and jur.status = 1 and jur.operacao = 'I'    
order by p.STATUS
) logj

Mas o meu grande impeditivo é o fato de ser o SQL 2000.

Answer 7

Deleted

Answer 8

Bom dia,

Guilherme, não sei se vai rodar no 2000, mas segue uma sugestão que talvez seja uma alternativa para obter o resultado desejado:

SELECT *
FROM
(
    SELECT 
        h.*,
        (SELECT TOP 1 jur.IdPessoa_log
         FROM pessoa_log jur    
         left join pessoa p 
             on p.cd_pessoa = jur.cd_pessoa    
         where 
             h.codcliente = jur.cd_pessoa_na and
             jur.status = 1 and 
             jur.operacao = 'I'    
         order by 
             p.STATUS) as IdPessoa_log
    FROM históricoCliente h
) as d
INNER JOIN pessoa_log jur    
    ON jur.IdPessoa_log = d.IdPessoa_log

Espero que seja útil.

---

Assinatura: http://www.imoveisemexposicao.com.br

Answer 9

José, 

Deixa eu explicar o cenário, acho que fica mais fácil.

Eu estou elaborando essa query, porque existem casos de duplicidade em parte do processo de carga que é realizado aqui, enquanto estão procurando a fonte da duplicidade, eu pretendo tratar isso na criação da view que utiliza parte desse select que eu postei, pois está influenciando em alguns relatórios.

O motivo do order by é que na tabela pessoa eu posso ter mais de 1 registro, nesse caso eu preciso pegar sempre o ativo, caso tenha apenas 1, eu seleciono ele mesmo, independente do status.

Ele pode me retornar nulo, pois na verdade na tabela historicoCliente, eu faço uma distinção entre pessoas físicas e jurídicas(um campo para cada), o join é na mesma tabela, alterando somente as chaves, e caso um desses dois campos me retorne nulo, eu trato os mesmos.

Para cada linha da tabela Pessoa, existe uma na tabela Pessoa_log, pois ele registra as ações realizadas naquele registro, o relacionamento é de 1:N

O meu select apenas do que eu passei no join é esse, passei um código no where para demonstrar o resultado:

SELECT * from 
pessoa_log pls
left join pessoa p on p.cd_pessoa = pls.cd_pessoa
where cd_pessoa_na = 303515349 and pls.status = 1 and pls.operacao = 'I'
order by p.status

E esse é o resultado que estou tentando obter:

SELECT TOP 1 * from 
pessoa_log pls
left join pessoa p on p.cd_pessoa = pls.cd_pessoa
where cd_pessoa_na = 303515349 and pls.status = 1 and pls.operacao = 'I'
order by p.status

Answer 10

Pessoal, estou colocando a query completa:

 SELECT
Dt_Log = getdate()
,Dt_Envio = getdate()
,Cd_Chave_Negocio = h.CodRealizacao
,Sistema = 'ALI - Cliente Nacional'
,Cd_Pessoa = isnull(isnull(logf.cd_pessoa,duplf.cd_pessoa),0)
,Operacao = 'I'
,Status = case when isnull(isnull(logf.cd_pessoa,duplf.cd_pessoa),0) = 0 or isnull(isnull(logj.cd_pessoa,duplj.cd_pessoa),0) = 0 or c.Cd_Acao is null or pl.cd_plano is null then 2 else 1 end
,Cd_Plano_NA = h.CodProjeto
,Cd_Acao_NA = t.CodAcao
,Cd_Acao = c.cd_acao
,Cd_Plano = pl.cd_plano
,Texto_Log = Case when c.Cd_Acao is null or pl.cd_plano is null then 'Plano Orçamentário não encontrado na base do Cliente-SP!!!' ELSE '' end+
Case when isnull(h.codempreedimento,0) = 0 then 'PJ - Lançamento realizado sem informação do código da Pessoa Jurídica' else
Case when isnull(h.codcliente,0) = 0 then 'PF - Lançamento realizado sem informação do código da Pessoa Física' else
 case when parcf.codparceiro is null then 'PF - Código não encontrado na Base do Cliente-NA!!!' else 
case when parcj.codparceiro is null then 'PJ - Código não encontrado na Base do Cliente-NA!!!' else
 case when isnull(isnull(logf.cd_pessoa,duplf.cd_pessoa),0) = 0 then
case WHEN NOT EXISTS (select 1 from pessoa (nolock) where cgc_cpf = right(REPLICATE('0',11)+rtrim(ltrim(parcf.cgccpf)),11)) then
'PF - Pessoa Física não cadastrada no SAC!!!' else 'PF - Código não referenciado na Base Cliente-SP!!!' end else ''
end+
 
case when isnull(isnull(logj.cd_pessoa,duplj.cd_pessoa),0) = 0 then
Case WHEN NOT EXISTS (select 1 from pessoa (nolock) where cgc_cpf = right(REPLICATE('0',14)+rtrim(ltrim(parcj.cgccpf)),14)) then
'PJ - Pessoa Jurídica não cadastrada no SAC!!!' else 'PJ - Código não referenciado na Base Cliente-SP!!!' end else ''
 end
end
 end
end
 end
,Cd_Pessoa_NA = h.codcliente
,Cd_Unidade = isnull(unop.cd_unidade,49)
,Mes = month(h.mesanocompetencia)
,Ano = year(h.mesanocompetencia)
,Dt_Evento = h.mesanocompetencia
,Cd_Pessoa_Empr = isnull(isnull(logj.cd_pessoa,duplj.cd_pessoa),0)
,Cd_Pessoa_Empr_NA = h.codempreedimento
,Nome_Produto = h.NomeRealizacao
,Cd_Meta_Fisica = case when isnull(isnull(logf.cd_pessoa,duplf.cd_pessoa),0) <> isnull(isnull(logj.cd_pessoa,duplj.cd_pessoa),0) and isnull(isnull(logj.cd_pessoa,duplj.cd_pessoa),0) <> 0 then
-- PJ
case when h.instrumento = 'Orientação Técnica a Distância' then 322 else 102 end
 else
--PF
case when h.instrumento = 'Orientação Técnica a Distância' then 321 else 101 end
 end
,Tipo_Pessoa = h.TipoPessoa
,Cd_Turma = h.CodRealizacao
,CodEvento = null -- verificar se deve ser o codrealizacao
,HORAS = hatv.CargaHoraria * 60
,Dt_Evento_Inicio = h.DataHoraInicioRealizacao
,IDPRODUTO = 0
,ID_LOCAL_ATEND = 0
,ID_SUBLOCAL_ATEND = 0
,CD_LOCAL = 0
,ID_LOCAL = 0
,CodProdutoPortfolio = 0
,IdAtendimento = h.CodRealizacao
,Acao_Comp_Fechada = ''
,Inf_Orient = ''
,Canal = ''
,Cd_Pessoa_Usuario = null
,Login_Usuario = null
,Nome_Usuario = null
,IdBaseConhecimentoDetalhe = null
,IdCanalAtendimento = null
,CNPJ                       = right(REPLICATE('0',14)+rtrim(ltrim(parcj.cgccpf)),14)
,CPF = right(REPLICATE('0',11)+rtrim(ltrim(parcf.cgccpf)),11)
from 
siacnet..historicorealizacoescliente h   (Nolock)

    left join 
    (SELECT TOP 1 jur.* FROM pessoa_log_siacnet jur (Nolock)  left join pessoa p on p.cd_pessoa = jur.cd_pessoa ORDER BY p.STATUS)
    logj ON (h.codempreedimento = logj.cd_pessoa_na and logj.status = 1 and logj.operacao = 'I')
    left join pessoa_log_siacnet_Dupl duplj  (Nolock) on duplj.cd_pessoa_na = h.codempreedimento and duplj.status = 1 and duplj.operacao = 'I'
    left join siacnet..parceiro parcj (Nolock) on parcj.codparceiro = h.codempreedimento
    left join 
    (SELECT TOP 1 fis.* FROM pessoa_log_siacnet fis (Nolock) left join pessoa p on p.cd_pessoa = fis.cd_pessoa ORDER BY p.STATUS)
    logf on (logf.cd_pessoa_na = h.codcliente and logf.status = 1 and logf.operacao = 'I')
    left join pessoa_log_siacnet_Dupl duplf  (Nolock) on duplf.cd_pessoa_na = h.codcliente and duplf.status = 1 and duplf.operacao = 'I'
    left join siacnet..parceiro parcf (Nolock) on parcf.codparceiro = h.codcliente
    left join SPSVSQL07.SAPT_SIORC.dbo.Apt_Plano_Ano pl on pl.cd_plano_na = h.CodProjeto and pl.ano = (select year(Dt_Inicio_Exercicio) from META_FISICA_EXERCICIO)
    left Join SiacNet.dbo.TbPaiAcao t   (Nolock) On (t.CodAcao_seq=h.CodAcao)
    Left Join SPSVSQL07.SAPT_SIORC.dbo.Apt_Acao_Ano c On (c.Cd_Acao_NA=t.CodAcao) And (c.Ano=(select year(Dt_Inicio_Exercicio) from META_FISICA_EXERCICIO))
    Left Join SPSVSQL07.SAPT_SIORC.dbo.Apt_Acao b On (b.Cd_Acao=c.cd_acao)          
left join spsvsql07.sapt_siorc.dbo.APT_PROCESSO pro on pro.CD_PROCESSO_RM = convert(int,RIGHT(ltrim(rtrim(b.centro_custo)),3))
left join spsvsql07.sapt_siorc.dbo.APT_PROCESSO_UNOP unop on unop.CD_PROCESSO = pro.cd_processo and unop.ativo = 'S'
left join siacnet..historicorealizacoescliente hatv (Nolock) on hatv.codcliente = h.codcliente 
and hatv.codempreedimento = h.codempreedimento 
and hatv.codrealizacao = h.codrealizacao 
and year(hatv.mesanocompetencia) = year(h.mesanocompetencia) 
and hatv.tiporealizacao = 'ATV' 
where 
      year(h.mesanocompetencia) = (select year(Dt_Inicio_Exercicio) from META_FISICA_EXERCICIO)
      and h.tiporealizacao = 'ALI'
      and h.codCliente = 30

Obrigado.

Answer 11

Pessoal,

Alguém tem alguma luz ???? 

Answer 12

Guilherme, você tentou utilizar a forma que sugeri? O que ocorreu? Não funcionou? Por que?

---

Assinatura: http://www.imoveisemexposicao.com.br

Answer 13

Guilherme, experimente fazer um teste com o script abaixo:

declare @Pessoa table
(Cd_Pessoa int, Nome_Pessoa varchar(55), Status char(1));

insert into @Pessoa values
(1000, 'Fred', 'C'),
(2000, 'Neymar', 'A'),
(3000, 'Luis', 'B'),
(4000, 'Julio', 'B'),
(5000, 'Daniel', 'C'),
(6000, 'Oscar', 'A');

declare @Pessoa_log table
(IdPessoa_log int, Cd_Pessoa int, Cd_Pessoa_NA int);

insert into @Pessoa_log values
(11, 1000, 123),
(12, 2000, 123),
(13, 3000, 123),
(14, 4000, 456),
(15, 5000, 456),
(16, 6000, 456);

declare @HistoricoCliente table
(CodCliente int, Data Datetime);

insert into @HistoricoCliente values
(123, '20140319'),
(456, '20140320');

select *
from
(
    select
        h.*,
        (SELECT TOP 1 jur.IdPessoa_log
         FROM @Pessoa_log jur    
         left join @Pessoa p 
             on p.Cd_Pessoa = jur.Cd_Pessoa    
         where 
             h.CodCliente = jur.Cd_Pessoa_NA    
         order by 
             p.Status) as IdPessoa_log
    from @HistoricoCliente as h
) as d
inner join @Pessoa_log jur    
    on jur.IdPessoa_log = d.IdPessoa_log
inner join @Pessoa p
    on p.Cd_Pessoa = jur.Cd_Pessoa

Espero que seja útil.

---

Assinatura: http://www.imoveisemexposicao.com.br

Answer 14

Gapimex,

Eu vou fazer um teste e retorno com o resultado.

Obrigado pela ajuda.

Answer 15

Pessoal, estou colocando a query completa:

 SELECT
Dt_Log = getdate()
,Dt_Envio = getdate()
,Cd_Chave_Negocio = h.CodRealizacao
,Sistema = 'ALI - Cliente Nacional'
,Cd_Pessoa = isnull(isnull(logf.cd_pessoa,duplf.cd_pessoa),0)
,Operacao = 'I'
,Status = case when isnull(isnull(logf.cd_pessoa,duplf.cd_pessoa),0) = 0 or isnull(isnull(logj.cd_pessoa,duplj.cd_pessoa),0) = 0 or c.Cd_Acao is null or pl.cd_plano is null then 2 else 1 end
,Cd_Plano_NA = h.CodProjeto
,Cd_Acao_NA = t.CodAcao
,Cd_Acao = c.cd_acao
,Cd_Plano = pl.cd_plano
,Texto_Log = Case when c.Cd_Acao is null or pl.cd_plano is null then 'Plano Orçamentário não encontrado na base do Cliente-SP!!!' ELSE '' end+
Case when isnull(h.codempreedimento,0) = 0 then 'PJ - Lançamento realizado sem informação do código da Pessoa Jurídica' else
Case when isnull(h.codcliente,0) = 0 then 'PF - Lançamento realizado sem informação do código da Pessoa Física' else
 case when parcf.codparceiro is null then 'PF - Código não encontrado na Base do Cliente-NA!!!' else 
case when parcj.codparceiro is null then 'PJ - Código não encontrado na Base do Cliente-NA!!!' else
 case when isnull(isnull(logf.cd_pessoa,duplf.cd_pessoa),0) = 0 then
case WHEN NOT EXISTS (select 1 from pessoa (nolock) where cgc_cpf = right(REPLICATE('0',11)+rtrim(ltrim(parcf.cgccpf)),11)) then
'PF - Pessoa Física não cadastrada no SAC!!!' else 'PF - Código não referenciado na Base Cliente-SP!!!' end else ''
end+
 
case when isnull(isnull(logj.cd_pessoa,duplj.cd_pessoa),0) = 0 then
Case WHEN NOT EXISTS (select 1 from pessoa (nolock) where cgc_cpf = right(REPLICATE('0',14)+rtrim(ltrim(parcj.cgccpf)),14)) then
'PJ - Pessoa Jurídica não cadastrada no SAC!!!' else 'PJ - Código não referenciado na Base Cliente-SP!!!' end else ''
 end
end
 end
end
 end
,Cd_Pessoa_NA = h.codcliente
,Cd_Unidade = isnull(unop.cd_unidade,49)
,Mes = month(h.mesanocompetencia)
,Ano = year(h.mesanocompetencia)
,Dt_Evento = h.mesanocompetencia
,Cd_Pessoa_Empr = isnull(isnull(logj.cd_pessoa,duplj.cd_pessoa),0)
,Cd_Pessoa_Empr_NA = h.codempreedimento
,Nome_Produto = h.NomeRealizacao
,Cd_Meta_Fisica = case when isnull(isnull(logf.cd_pessoa,duplf.cd_pessoa),0) <> isnull(isnull(logj.cd_pessoa,duplj.cd_pessoa),0) and isnull(isnull(logj.cd_pessoa,duplj.cd_pessoa),0) <> 0 then
-- PJ
case when h.instrumento = 'Orientação Técnica a Distância' then 322 else 102 end
 else
--PF
case when h.instrumento = 'Orientação Técnica a Distância' then 321 else 101 end
 end
,Tipo_Pessoa = h.TipoPessoa
,Cd_Turma = h.CodRealizacao
,CodEvento = null -- verificar se deve ser o codrealizacao
,HORAS = hatv.CargaHoraria * 60
,Dt_Evento_Inicio = h.DataHoraInicioRealizacao
,IDPRODUTO = 0
,ID_LOCAL_ATEND = 0
,ID_SUBLOCAL_ATEND = 0
,CD_LOCAL = 0
,ID_LOCAL = 0
,CodProdutoPortfolio = 0
,IdAtendimento = h.CodRealizacao
,Acao_Comp_Fechada = ''
,Inf_Orient = ''
,Canal = ''
,Cd_Pessoa_Usuario = null
,Login_Usuario = null
,Nome_Usuario = null
,IdBaseConhecimentoDetalhe = null
,IdCanalAtendimento = null
,CNPJ                       = right(REPLICATE('0',14)+rtrim(ltrim(parcj.cgccpf)),14)
,CPF = right(REPLICATE('0',11)+rtrim(ltrim(parcf.cgccpf)),11)
from 
siacnet..historicorealizacoescliente h   (Nolock)

    left join 
    (SELECT TOP 1 jur.* FROM pessoa_log_siacnet jur (Nolock)  left join pessoa p on p.cd_pessoa = jur.cd_pessoa ORDER BY p.STATUS)
    logj ON (h.codempreedimento = logj.cd_pessoa_na and logj.status = 1 and logj.operacao = 'I')
    left join pessoa_log_siacnet_Dupl duplj  (Nolock) on duplj.cd_pessoa_na = h.codempreedimento and duplj.status = 1 and duplj.operacao = 'I'
    left join siacnet..parceiro parcj (Nolock) on parcj.codparceiro = h.codempreedimento
    left join 
    (SELECT TOP 1 fis.* FROM pessoa_log_siacnet fis (Nolock) left join pessoa p on p.cd_pessoa = fis.cd_pessoa ORDER BY p.STATUS)
    logf on (logf.cd_pessoa_na = h.codcliente and logf.status = 1 and logf.operacao = 'I')
    left join pessoa_log_siacnet_Dupl duplf  (Nolock) on duplf.cd_pessoa_na = h.codcliente and duplf.status = 1 and duplf.operacao = 'I'
    left join siacnet..parceiro parcf (Nolock) on parcf.codparceiro = h.codcliente
    left join SPSVSQL07.SAPT_SIORC.dbo.Apt_Plano_Ano pl on pl.cd_plano_na = h.CodProjeto and pl.ano = (select year(Dt_Inicio_Exercicio) from META_FISICA_EXERCICIO)
    left Join SiacNet.dbo.TbPaiAcao t   (Nolock) On (t.CodAcao_seq=h.CodAcao)
    Left Join SPSVSQL07.SAPT_SIORC.dbo.Apt_Acao_Ano c On (c.Cd_Acao_NA=t.CodAcao) And (c.Ano=(select year(Dt_Inicio_Exercicio) from META_FISICA_EXERCICIO))
    Left Join SPSVSQL07.SAPT_SIORC.dbo.Apt_Acao b On (b.Cd_Acao=c.cd_acao)          
left join spsvsql07.sapt_siorc.dbo.APT_PROCESSO pro on pro.CD_PROCESSO_RM = convert(int,RIGHT(ltrim(rtrim(b.centro_custo)),3))
left join spsvsql07.sapt_siorc.dbo.APT_PROCESSO_UNOP unop on unop.CD_PROCESSO = pro.cd_processo and unop.ativo = 'S'
left join siacnet..historicorealizacoescliente hatv (Nolock) on hatv.codcliente = h.codcliente 
and hatv.codempreedimento = h.codempreedimento 
and hatv.codrealizacao = h.codrealizacao 
and year(hatv.mesanocompetencia) = year(h.mesanocompetencia) 
and hatv.tiporealizacao = 'ATV' 
where 
      year(h.mesanocompetencia) = (select year(Dt_Inicio_Exercicio) from META_FISICA_EXERCICIO)
      and h.tiporealizacao = 'ALI'
      and h.codCliente = 30

Obrigado.

Guilherme,

Cara na boa, observe o nível de complexidade da sua query, existem diversas condições que o SQL Server tem que analisar, dizer que a culpa é do SQL Server 2000 é algo que não podemos justificar.

O que você tem que analisar é a maneira que os dados estão sendo obtidos.

---

Pedro Antonio Galvão Junior [MVP | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados | SorBR.Net | Professor Universitário | MSIT.com]