Doubt with LEFT or INNER JOIN and where SQL Access

Question

Doubt with LEFT or INNER JOIN and where SQL Access

Everything Okay?

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_precos_special, it has an ID_CLiFor field, and I want to put a WHERE at the end of the query to filter the results by this 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 brings only the results where in the tbl_precos_special the ID_CliFor is 268:

But I wanted it to bring all the results from tbl_products as well. Is that in tbl_precos_specialis will have several ID_CliFor, and I will filter for the specific client what it already has in that table but also the products it does not have yet so that data can be added.

I don't understand much of SQL and would appreciate a help.

Grateful

Fabio

0

sql join ms-access query where

Author: Fabio Gomes, 2020-12-03

Source

1 answers

score 1 · Answer 1

Fabio, the value of the column ID_CliFor of the table tbl_precos_speciales will be null when there is no row in that 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;

Hope it helps