How to bring records of a LEFT JOIN even if not obeying the WHERE?

Question

Exemplifying, I have the

tabela_A:

cod     nome
1       stack
2       overflow
3       stackoverflow

and tabela_B:

cod_tabela_A    ano     mes valor
1               2016    1   100     
1               2016    2   115
2               2016    1   90

When made a LEFT JOIN, returns me the following, correctly:

SELECT * FROM tabela_A a
LEFT JOIN tabela_B b ON a.cod = b.cod_tabela_A

cod     nome            cod_tabela_A    ano     mes     valor
1       stack           1               2016    1       100     
1       stack           1               2016    2       100     
2       overflow        2               2016    1       90
3       stackoverflow   NULL            NULL    NULL    NULL

If I add the WHERE clause:

SELECT * FROM tabela_A a
LEFT JOIN tabela_B b ON a.cod = b.cod_tabela_A
WHERE ano = 2016 AND mes = 2

Returns me a single record, correctly:

cod     nome            cod_tabela_A    ano     mes valor
1       stack           1               2016    2   100     

But what I need is that when I do not obey the values indicated in WHERE, return me NULL:

cod     nome            cod_tabela_A    ano     mes     valor
1       stack           NULL            NULL    NULL    NULL        
1       stack           1               2016    2       NULL        
2       overflow        NULL            NULL    NULL    NULL
3       stackoverflow   NULL            NULL    NULL    NULL

Is there any way to get this result?

Answer 1

Practically you just need to remove the Where, because it makes the final filter, that is, only the lines that have the filter Where will resume , if you use the AND shortly after the ON of LEFT JOIN everything will be returned from tabela_A and the conditions of tabela_B.

ON a.Cod = b.cod_table_A and year = 2016 AND mes = 2

declare @tabela_A table
(
    cod int,
    nome varchar(100)
)

declare @tabela_B table
(
    cod_tabela_A     int,
    ano int,
    mes int,
    valor int
)

insert into @tabela_B values
(1        ,       2016   , 1  , 100  ),   
(1     ,          2016  ,  2  , 115),
(2   ,            2016  ,  1  , 90)

insert into @tabela_A values
(1 ,      'stack'),
(2 ,      'overflow'),
(3 ,     ' stackoverflow')


SELECT * FROM @tabela_A a
LEFT JOIN @tabela_B b ON a.cod = b.cod_tabela_A
and ano = 2016 AND mes = 2

Answer 2

I believe that the code below solves your problem, just a hint, I know you want to return all fields of both tables but sometimes two tables have some field with the same name and ends up giving duplicate field error. I’ve been working with databases for years and I see it happen a lot. Follow what you need, put using the standardization that I use in the company.

 SELECT A.cod AS codA, A.nome AS nomeA, 
        B.cod AS codB, B.codigo_tabela_a AS cod_tb_A, 
        B.ano AS anoB, B.mes AS mesB, 
        B.valor AS valB
 FROM @tabela_a A
 LEFT JOIN tabela_b B ON A.cod = B.cod_tabela_a AND B.ano = 2016 AND B.mes = 2

Answer 3

Hello, you can wear IF without the WHERE.
You will bring all the results, being that
IF meet the criterion (b.ano = 2016 AND b.mes = 2) returns the original value,
otherwise NULL.

*Separated with blank lines to facilitate understanding.

SELECT a.Cod, a.name,
IF( b. year = 2016 AND b.mes = 2, b.cod_table_A, NULL) AS cod_table_A,
IF( b. year = 2016 AND b.mes = 2, b.year, NULL) AS year,
IF( b. year = 2016 AND b.mes = 2, b.mes, NULL) AS month,
IF( b. year = 2016 AND b.mes = 2, b.value, NULL) AS value
FROM table a
LEFT JOIN table_B b ON a.Cod = b.cod_table_A

Where I put NULL you can use another value like ' ' to leave blank.
IF( b.ano = 2016 AND b.mes = 2, b.ano, ' ')

Answer 4

I did it that way and it worked, but I don’t know if I’d see you

declare @retorna_dado int,
@ano int,
@mes int
set @ano = 2016
set @mes = 2
set @retorna_dado = (SELECT COUNT(*) FROM tabela_A a
LEFT JOIN tabela_B b ON a.cod = b.cod_tabela_A
WHERE ano = @ano AND mes = @mes)

print @retorna_dado

if (@retorna_dado > 0)

SELECT * FROM tabela_A a
LEFT JOIN tabela_B b ON a.cod = b.cod_tabela_A
WHERE ano = @ano AND mes = @mes

else
SELECT * FROM tabela_A a
LEFT JOIN tabela_B b ON a.cod = b.cod_tabela_A

If Voce declares the year 2016 and month 02 (or anyone that exists in the table) it will bring with Where, if it does not exist it will ignore Where

See if it helps