Doubt with LEFT or INNER JOIN and WHERE SQL Access

Question

all right?

So, I have this query here in Access:

SELECT tbl_produtos.NomeProduto, tbl_produtos.PrecoVenda, tbl_precos_especiais.PrecoVenda
FROM tbl_produtos LEFT JOIN tbl_precos_especiais ON tbl_precos_especiais.ID_Produto = tbl_produtos.ID_Produto;

But in tbl_special prices, there is an Id_clifor field, and I want to put a WHERE at the end of the query to filter the results through that field:

SELECT tbl_produtos.NomeProduto, tbl_produtos.PrecoVenda, tbl_precos_especiais.PrecoVenda
FROM tbl_produtos LEFT JOIN tbl_precos_especiais ON tbl_precos_especiais.ID_Produto = tbl_produtos.ID_Produto WHERE tbl_precos_especiais.ID_CliFor = 268;

However, it only brings the results where in tbl_special prices Id_clifor is 268:

But I wanted him to bring all the results of tbl_products too. Is that in tbl_precos_especiais will have several Id_clifor, and I will filter for the specific customer what he already has in this table but also the products that have not yet to be added data.

I don’t know much about SQL and would appreciate a little help.

Grateful

Fabio

Answer 1

Fabio, the value of the Id_clifor column of the table tbl_special precos_will be null when there is no row in this table for the product since it is being used Left Join, so try leaving Where as follows:

WHERE tbl_precos_especiais.ID_CliFor = 268 OR tbl_precos_especiais.ID_CliFor IS NULL;

I hope it helps