Answering my own question:
In order to display a row that does not contain data, one must use an
outer join, but the question was what to join to. I created a table
named "numbers" with a single column named "number". I gave the table
eight rows, with the numbers 1 through 8. Then, the following query
worked:
select charge.charge, bases.base, bases.base_type, number, coil_id,
stack, stack_position
from charge
inner join bases on charge.base = bases.base
inner join base_type on bases.base_type = base_type.base_type
inner join numbers on number <= base_type.number_stacks
left outer join inventory on inventory.stack = number and
inventory.charge = charge.charge
where charge.charge = 37900
RobR
-----Original Message-----
Greetings!
A charge consists of a set of coils arranged in one or more stacks. It
is possible for a stack to be empty. I need a query that lists all
possible stacks, along with the coils that are in each stack (if any).
Charge 1 can have 4 stacks. Only stacks 2 and 3 are in use. A plain
ordinary query can get me:
Stack Coil Position
2 A 1
2 B 2
3 C 1
3 D 2
I need:
Stack Coil Position
1
2 A 1
2 B 2
3 C 1
3 D 2
4
The query is allowed to include the maximum possible number of stacks,
which is 8.
How can I get a result like this?
Thanks very much!
RobR