SQL - Insert constant based on the presence of categorical value

Question

I have a table like the following in MySQL:

ID	COMPONENT	AMOUNT
123	C1	12
123	C2	15.5
123	C3	13
234	C1	544
234	C2	546
445	C1	142
334	C1	13

And would like to obtain something like this using an SQL SELECT:

ID	COMPONENT	AMOUNT
123	C1	12
123	C2	15.5
123	C3	13
234	C1	544
234	C2	546
234	C3	0
445	C1	142
445	C2	0
445	C3	0
334	C1	13
334	C2	0
334	C3	0

Meaning I would like to show rows with AMOUNT 0 for the components that do not exist for a given ID.

Answer 1

You need a CROSS join of the distinct IDs to the distinct COMPONENTs and a LEFT join to the table:

SELECT i.ID, c.COMPONENT,
       COALESCE(t.AMOUNT, 0) AMOUNT
FROM (SELECT DISTINCT ID FROM tablename) i
CROSS JOIN (SELECT DISTINCT COMPONENT FROM tablename) c
LEFT JOIN tablename t ON t.ID = i.ID AND t.COMPONENT = c.COMPONENT
ORDER BY i.ID, c.COMPONENT;

See the demo.