Sweetz1

Create a table holding all possible stack positions. Use a LEFT JOIN along with a sub-query to get all stack positions of a pallet, whether the row exists or not.

You must execute the code again to save those values in the database.

-- Create sample parts table. Pallet 1 has 8 pieces, pallet 2 has 3 pieces.
CREATE TABLE parts (
  layer INT,
  layer_pos INT, 
  pallet INT, 
  stack_pos INT,
  serial INT);

INSERT INTO parts(layer, layer_pos, pallet, stack_pos, serial) 
	VALUES (1,1,1,1,111),
           (1,1,1,2,122),
           (1,1,1,3,133),
           (1,1,1,4,144),
           (1,1,1,5,155),
           (1,1,1,6,166),
           (1,1,1,7,177),
           (1,1,1,8,188),
	       (1,1,2,1,211),
           (1,1,2,2,222),
           (1,1,2,3,233);

-- CREATE stack lookup table that holds all possible stack_pos values
 CREATE TABLE stack_lookup (
  stack_pos INT);

INSERT INTO stack_lookup(stack_pos) 
	VALUES (1),(2),(3),(4),(5),(6),(7),(8);

-- QUERY database
SELECT s.stack_pos, COALESCE(subq.serial,0) as serial
FROM stack_lookup s 
LEFT JOIN (SELECT stack_pos, serial 
           FROM parts 
           WHERE layer=1
           AND layer_pos=1
           AND pallet=2) subq 
     ON s.stack_pos = subq.stack_pos
ORDER BY s.stack_pos;

stack_pos	serial
1	211
2	222
3	233
4	0
5	0
6	0
7	0
8	0

(11 rows affected)

(8 rows affected)

(8 rows affected)