How to interpret * in this query

Question

How to interpret * in this query

Navigation

#1 by (2 votes)

0

I have here a% w / o%, which apparently is simple if it were not for the fact that I have these query ai, which I can not tell whether it is * , inner join or some other in the same family .

I need to convert this left join to query , and the result is not beating right. Does anyone help me to interpret this? I did linq to sql and did not hit it right.

select AFENTIDADE.NOMEENTIDADE, NOMEAO, NOMEUTILIZADOR,
MN.DATACRIA, MN.DATAFECHO, MN.NUMFACT,
MN.NUMFOLHA, AESERVICO.TempoEI, HORASA, R.PRECOD
From AEMANUTE MN WITH (NOLOCK), AFENTIDADE WITH (NOLOCK), AREGDOC R WITH (NOLOCK),
UTILIZADORES WITH (NOLOCK), AESERVICO WITH (NOLOCK),  AIAREAORGANICA WITH (NOLOCK)
Where  (MN.CODENTIDADE=AFENTIDADE.CODENTIDADE
And MN.CODAO=AIAREAORGANICA.CODAO
AND MN.CODAO *=R.CODAO
And MN.numfolha=aeservico.numfolha
And MN.numfact *= R.numfact
And aeservico.codutilizador=utilizadores.codutilizador
AND UTILIZADORES.ANULADO IS NULL 
AND
R.coddoc='FM') And 
R.STATUS<>'E' And
MN.IdSituacao=5
--or MN.IdSituacao=5) 
And
LEFT(MN.datafecho, 10) >='2017-04-01' And LEFT(MN.datafecho, 10)  <='2017-04-30' 
ORDER BY  NOMEAO, NOMEUTILIZADOR, AFENTIDADE.NOMEENTIDADE

sql linq inner

asked by anonymous 16.05.2017 / 17:44

1 answer

Integration Services Components - SSIS - Data Flow [closed] Php - Get variable by _GET or _POST? How to make name appear instead of cpf

score 2 · Answer 1

At first the * = construction is the same as LEFT JOIN.

Constructs of type

-- código #1
SELECT T1.codigo, T2.nome
  from tab1 as T1, tab2 as T2
  where T1.codigo *= T2.codigo;

are usually converted to

-- código #2
SELECT T1.codigo, T2.nome
  from tab1 as T1
       left outer join tab2 as T2 on T1.codigo = T2.codigo;

However, the result returned may be slightly different, depending on the other conditions involved and the DBMS.

Here is a suggested conversion for your code; evaluate it carefully as it has not been tested.

-- código #3
SELECT ...
  from  AEMANUTE as MN
        inner join AFENTIDADE on AFENTIDADE.CODENTIDADE = MN.CODENTIDADE
        left outer join AREGDOC as R on R.CODAO = M.CODAO and R.numfact = MN.numfact
        inner join AESERVICO on aeservico.numfolha = MN.numfolha
        inner join UTILIZADORES on utilizadores.codutilizador = aeservico.codutilizador
        inner join AIAREAORGANICA on AIAREAORGANICA.CODAO = MN.CODAO
  where UTILIZADORES.ANULADO IS NULL 
        and (R.coddoc is null or R.coddoc = 'FM')
        and (R.STATUS is null or R.STATUS <> 'E')
        and MN.IdSituacao = 5
        and LEFT(MN.datafecho, 10) >='2017-04-01' And LEFT(MN.datafecho, 10)  <='2017-04-30' 
  order by NOMEAO, NOMEUTILIZADOR, AFENTIDADE.NOMEENTIDADE;