Ismael Oliveira

Ismael Oliveira

  • 1.6k
  • 122
  • 9.4k

Query not working

Jun 5 2021 2:27 PM
Hi.
I'm developing a forms system in C# and have to retrieve data from a SQLServer Database. There are 2 tables: Items and Inventory. In Items table there are products, which have a code of 1 and services, which have a code of 2. I must create a table that retrieves all products in Items table and relate them to the data in Inventory table. The problem is that maybe there are no data in this table. In this case I must bring a table with all items and fields concerning inventory empty. I tried the following query:
 
select I.ITEM_NA_CODIGO,
I.ITEM_TX_NOME, I.ITEM_TX_UNIDADE,
isnull(cast(IV.INVE_RE_QUANTIDADE as varchar(12)), '') as Quant,
isnull(cast(IV.INVE_RE_PRECOMEDIO as varchar(12)), '') as Preço
from ITEM I
full outer join INVENTARIO IV
on IV.ITEM_NA_CODIGO = I.ITEM_NA_CODIGO
and I.ITEM_NI_TIPOITEM = 1
and IV.INVE_DT_DATAINVENTARIO = '31/05/2021'
 
The result can be seen below: 
 
 
The problem is that rows numbered from 5 to 8 are services and should not appear. 
In the query, line "and I.ITEM_NI_TIPOITEM = 1" should guarantee this, but is not working.
Does anyone have any idea of my mistake?
Thanks.

Answers (2)