Re: Query help, please

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