Condition under different tables

[gtsolid]

Good morning,
I'd like to use the same condition about quantity I use to discrimate di first table also in the second (instead of NOT EXISTS).

For example with a fixed quantity, i don't need the double discrimination:

SELECT m.Codice, m.QntFROM Magazzino AS m
WHERE m.[Prossimo_arrivo] Is Null AND
      ( (m.Qnt < 35 AND m.[Fascia_I] = True) OR
        (m.Qnt < 15 AND m.[Fascia_II] = True)
      ) AND
      NOT EXISTS (SELECT 1
                  FROM Magazzino_Grezzi as mg
                  WHERE mg.Codice = m.Codice
                 );

How can I modify my current string?
this is not working:

SELECT m.Codice, m.QntFROM Magazzino AS m
WHERE m.[Prossimo_arrivo] Is Null AND
      ( (m.Qnt < 35 AND m.[Fascia_I] = True) OR
        (m.Qnt < 15 AND m.[Fascia_II] = True)
      ) AND
      (SELECT 1
                  FROM Magazzino_Grezzi as mg
                  WHERE mg.Codice = m.Codice AND
                  (mg.Qnt < 10)
                 );

[ArviLaanemets]

The outcome is determined by WHERE condition you use (either "Is Null" or "Is Not Null")!

SELECT m.Codice, m.Qnt
FROM Magazzino m
LEFT JOIN Magazzino_Grezzi mg ON mg.Codice = m.Codice
WHERE mg.Codice Is Null/Is Not Null

[gtsolid]

SELECT m.Codice, m.QntFROM Magazzino AS m
LEFT JOIN Magazzino_Grezzi mg ON mg.Codice = m.Codice
WHERE m.[Prossimo_arrivo] Is Null AND
      ( (m.Qnt < 35 AND m.[Fascia_I] = True) OR
        (m.Qnt < 15 AND m.[Fascia_II] = True)
      ) AND
        mg.Qnt < 10;

Some codes are not displayed, but I don't understand the reason.
Maybe I have to add something like THIS?

AND
		mg.Qnt < 10 OR NOT EXISTS;

[ArviLaanemets]

You can use expression like Nz(mg.Qnt,0) < 10, to have empty fields included.

[gtsolid]

It works. But why my solution don't? How does NZ works?

[ArviLaanemets]

It works. But why my solution don't? How does NZ works?

In case the field value is null, it is replaced in expression with value of 2nd parameter (in your case with 0)

NB! The 2nd parameter can be of any valid data type. E.g. you have a table of items with ItemID and Name, and sometimes the name field may be empty. In queries, you can display some text for such entries, like

SELECT ItemID, ItemCode, Nz(ItemName,"The Name is missing!") As ItemName FROM tblItems

About your solution not working: Records, where you are trying to do some mathematical/logical operations with Null (Empty) values, are left out from query result dataset

[gtsolid]

thanks, it works fine.