SQL Select with Join Bringing Odd Result

Question

I know this kind of problem can happen but I have no idea on figuring it out (also, it is in Portuguese, so I will try my best to illustrate my question in English, translating the database structure, fields, etc).

These are the 4 tables that I need for this query:
- Agenda_Cliente (this is the Client table);
- Agenda_Imposto (this is the Taxes table - description for each tax ID);
- Agenda_ClienteImposto (NxN table - relates each Client to the taxes he or she pays);
- Agenda_LogAgenda (that's the core of the query: it contains which tax (by ID: CodigoImposto) was sent to a specific client (by ID as well: CodigoCliente), when the tax was sent (DataHoraEnvio) and when it was accessed (DataHoraAcesso).

The strctures are the following:

CREATE TABLE [dbo].[Agenda_Cliente](
[CodigoCliente] [bigint] IDENTITY(1,1) NOT NULL,
[CodigoEscritorio] [int] NOT NULL,
[RazaoSocial] [varchar](60) NOT NULL,
[NomeFantasia] [varchar](60) NOT NULL,
[Email] [varchar](60) NOT NULL,
[TelefoneComercial] [bigint] NOT NULL,
[TelefoneCelular1] [bigint] NOT NULL,
[TelefoneCelular2] [bigint] NOT NULL,
[CnpjCpf] [bigint] NOT NULL,
[Cep] [int] NOT NULL,
[Endereco] [varchar](80) NOT NULL,
[Bairro] [varchar](60) NOT NULL,
[Municipio] [varchar](60) NOT NULL,
[Estado] [char](2) NOT NULL,
[RegimeTributacao] [int] NOT NULL,
[FlagAtivo] [bit] NOT NULL,
PRIMARY KEY CLUSTERED 
(
    [CodigoCliente] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[Agenda_Imposto](
[CodigoImposto] [int] IDENTITY(1,1) NOT NULL,
[DescricaoImposto] [varchar](30) NOT NULL,
[TipoImposto] [int] NOT NULL,
[DeptoResponsavel] [int] NOT NULL,
PRIMARY KEY CLUSTERED 
(
    [CodigoImposto] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[Agenda_ClienteImposto](
[CodigoClienteImposto] [bigint] IDENTITY(1,1) NOT NULL,
[CodigoCliente] [bigint] NOT NULL,
[CodigoImposto] [int] NOT NULL,
[DataLimite] [int] NOT NULL,
PRIMARY KEY CLUSTERED 
(
    [CodigoClienteImposto] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

Also, the FOREIGN KEYS are all OK and working.

So, the problem is: I have the following SQL SELECT query:

SELECT c.CnpjCpf, c.NomeFantasia, i.DescricaoImposto, ci.DataLimite, la.DataHoraEnvio, la.DataHoraAcesso
FROM Agenda_Cliente c
JOIN Agenda_ClienteImposto ci ON ci.CodigoCliente = c.CodigoCliente
JOIN Agenda_Imposto i ON i.CodigoImposto = ci.CodigoImposto
LEFT JOIN Agenda_LogAgenda la ON la.CodigoImposto = i.CodigoImposto
WHERE c.CodigoEscritorio = 1
ORDER BY c.NomeFantasia ASC, ci.DataLimite ASC

It's bringing me this:

But I need it to bring me this:

And if I SELECT * at the Calendar log (Agenda_LogAgenda), there's only one record.

It seems to me that it's something to do with Agenda_ClienteImposto, as far as I tried here, but it may be something about the JOIN clause that I'm not getting, because it's applying the result for "Empresa 1" in "Empresa 3", but "Empresa 3" does not exist in Agenda_LogAgenda.

Any help is appreciated. Thanks!

Answer 1

The left join to Agenda_LogAgenda is only on CodingoImposto. From the values in the query, 'CRF' probably does exist. Add NomFantasia to the ON clause of the left join.