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