Show all fields from table in select query ?

[Domino]

Hi all.

I have 3 tables - INVENTORY, IN and OUT

I made a select query to calculate the quantity of inventory on stock, but I
only see the inventory items that are in both IN or OUT tables.

If I select to show all fields from Inventory and only those related from
other tables a get null values.

Is there a way to have a field showing 0 (zero) instead of empty field ?

 

[Allen Browne]

You can use an outer join instead of an inner join (the default.)
Details in:
The Query Lost My Records! (Nulls)
at:
http://allenbrowne.com/casu-02.html

A better solution might be to combine the IN and OUT into one table. Not
only would this solve the problem that IN is a reserved word, and OUT is
also a future reserved word, but it would simplify your inventory no end.
Just add a Number type field to your table. Use 1 for incoming, and -1 for
outgoing. You can then multiply this by the Quantity, and you end up with
the net transaction.

The sample database in this link uses that approach:
http://allenbrowne.com/TechniqueEnterCalcText.html