Member Avatar for jean_5

I have the following view:

ALTER VIEW [dbo].[vw_RelInclusaoGeralCivil]
AS
SELECT   
DISTINCT   
    m.Nome AS MunicipioNome, 
    au.Nome UsuarioNome,
    s.TipoId AS SolicitacaoTipoId
FROM                    
    Solicitacao s
INNER JOIN
    Unidade u 
ON
    s.UnidadeEntregaId = u.Id
INNER JOIN
    Endereco e
ON
    u.EnderecoId = e.Id
INNER JOIN
    [IBGE.Municipio] m
ON
    e.CodMunicipio = m.CodMunicipio
INNER JOIN
    Terminal t
ON
    u.Id = t.UnidadeId
INNER JOIN
    Auditoria a
ON
    t.Id = a.TerminalId
INNER JOIN
    [Acesso.Usuario] au
ON
    a.UsuarioId = au.Id
WHERE
    s.TipoId <> 5
AND
    a.TerminalId IS NULL
GO

The Solicitacao table:

Id | UnidadeEntregaId | TipoId
2  |        4         |   1

The Unidade table:

Id | EnderecoId | 
4  |     5      |

The Endereco table:

Id | CodMunicipio | 
5  |  5208707     |

The IBGE.Municipio table:

CodMunicipio | Nome
5208707      | GOIANIA

The Terminal table(I need this table to get in the Acesso.Usuario table):

Id        | Codigo | 
1         | 0001   |

The Auditoria table(all TerminalId in Auditoria table are null, but i also need it to get in Acesso.Usuario):

TerminalId | UsuarioId |
NULL       |    31     |

And the Acesso.Usuario table:

Id | Nome
31 | Usuario Master

What can I do to show the Solicitacao in my view?
Thanks

  • 2 Contributors
  • 1 Reply
  • 286 Views
  • 2 Weeks Discussion Span
  • Latest Post Latest Post by bnmng

Dani AI

Generated

Good catch by — the INNER JOIN chain to Terminal → Auditoria → [Acesso.Usuario] forces every link to exist, so any missing or non-matching audit/terminal row will drop the Solicitacao row from the result. In the original code the WHERE check against a.TerminalId could never be true when Auditoria was inner-joined, which explains why no rows appeared.

Keep the required path (Solicitacao → Unidade → Endereco → Municipio) as INNER JOINs, but make the Terminal/Auditoria/Usuario part optional by using LEFT JOINs. After that change, detect “no audit” rows by testing a column from the joined audit (for example a primary key or a.UsuarioId) for NULL — that reliably finds solicitacoes without a matching audit. Be careful: putting other predicates on columns from the left-joined tables in the WHERE clause (for example au.Nome = 'X') will implicitly turn the LEFT JOIN back into an INNER JOIN; such filters should be moved into the ON clause or written to allow NULLs.

If the goal is different (for example: pick the latest audit per terminal, or include audit rows that have TerminalId NULL but are related by another field), use an OUTER APPLY / subquery to pull a single audit row per terminal, or UNION separate logic for “global” audits if there is no linking column. For debugging, run a minimal SELECT that returns s.Id plus the joined t.Id and a.UsuarioId (and remove DISTINCT) to see which join removes the row. If Auditoria has no field that can be tied to Unidade/Solicitacao except TerminalId, then an audit record with a NULL TerminalId cannot be associated without changing the schema or adding explicit linking logic.

Member Avatar for bnmng

You might get what you want by using a LEFT JOIN instead of the first INNER JOIN.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.