I have a table like the following in MySQL:

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

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

CodePudding user response：

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.

CodePudding user response：

Here is one way to do it:

select t1.id, 
       t1.component, 
       IFNULL(t2.amount,0) amount 
from (
     select a.id, b.component from table1 a
     left join (select distinct component from table1) b on 1=1
     group by a.id, b.component 
     ) t1
left join table1 t2 on t1.id=t2.id and t1.component=t2.component
order by t1.id, t1.component;

Explained: Step 1: The subselect puts together all unique combinations of id and components regardless of the amount values. Step 2: Do a left join to calculate the amount for each id/component combination.

This query works if you don't have a duplicate combination of id/component in your table. If you do have multiple values for each combination you can use an aggregate function like group by on the first two columns, and sum() on the amount column.

The result:

Dummy data:

CREATE TABLE table1 (
  id int(11),
  component varchar(10),
  amount float(5,1)
  );
  
INSERT INTO table1 VALUES
    (123, 'C1', 12),
    (123, 'C2', 15.5),
    (123, 'C3', 13),
    (234, 'C1', 544),
    (234, 'C2', 546),
    (445, 'C1', 142),
    (334, 'C1', 13);

View on DB Fiddle