Doubt with LEFT or INNER JOIN and WHERE SQL Access

Asked

Viewed 73 times

0

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;

Os resultados são esses

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: Resultados com WHERE

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

sql query join ms-access where

  • "However, it only brings the results where in tbl_special prices Id_clifor is 268" it is obvious because you put the clause WHERE tbl_precos_especiais.ID_CliFor = 268. Explain what you mean by "... but also the products you don’t have yet so you can add data". You don’t have the product where?

1 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

  • good, it worked, thank you!

Browser other questions tagged sql query join ms-access where

You are not signed in. Login or sign up in order to post.