join table with itself to create more row

Question

me has a table which has following data in TABLE1

me wanted to make sure all teh name has all "work type" available for all those dates only which is in teh data. Output should like TABLE2

me tried to put a query like

select t1.name,t1.date,mt.work_type,t1.minutes 
from table1 t1
right join (select distinct work_type from t1) mt on t1.work_type=mt.work_type

however it didn't work. Please halp

Answer 1

You need to join with a subquery that cross joins all the names, dates and work types.

SELECT mt.name, mt.date, mt.work_type, COALESCE(t1.minutes, 0) AS minutes
FROM (
    SELECT DISTINCT t1.name, t1.date, t2.work_type
    FROM table1 AS t1
    CROSS JOIN table1 AS t2
) AS mt
LEFT JOIN table1 AS t1 
    ON t1.name = mt.name AND t1.date = mt.date AND t1.work_type = mt.work_type
ORDER BY mt.name, mt.date, mt.work_type

Note that in the subquery you have to specify the actual table name, not the alias from the main query.

DEMO

Answer 2

In Oracle, you can use a partitioned outer join:

SELECT t.name,
       t."DATE",
       w.work_type,
       COALESCE(t.minutes, 0) AS minutes
FROM   (SELECT DISTINCT work_type FROM table1) w
       LEFT OUTER JOIN table1 t
       PARTITION BY (t.name, t."DATE")
       ON (w.work_type = t.work_type)

Which, for the sample data:

CREATE TABLE table1 (name, "DATE", work_type, minutes) AS
SELECT 'a', DATE '2011-01-01', 'labor',   53 FROM DUAL UNION ALL
SELECT 'a', DATE '2011-01-01', 'private', 58 FROM DUAL UNION ALL
SELECT 'a', DATE '2011-01-01', 'other',   19 FROM DUAL UNION ALL
SELECT 'b', DATE '2011-01-02', 'labor',   31 FROM DUAL UNION ALL
SELECT 'b', DATE '2011-01-02', 'other',   24 FROM DUAL UNION ALL
SELECT 'b', DATE '2011-01-01', 'private', 19 FROM DUAL UNION ALL
SELECT 'c', DATE '2011-01-03', 'labor',   25 FROM DUAL UNION ALL
SELECT 'c', DATE '2011-01-03', 'private', 50 FROM DUAL UNION ALL
SELECT 'c', DATE '2011-01-01', 'private', 23 FROM DUAL UNION ALL
SELECT 'd', DATE '2011-01-01', 'other',   20 FROM DUAL;

Outputs:

NAME	DATE	WORK_TYPE	MINUTES
a	01-JAN-11	labor	53
a	01-JAN-11	other	19
a	01-JAN-11	private	58
b	01-JAN-11	labor	0
b	01-JAN-11	other	0
b	01-JAN-11	private	19
b	02-JAN-11	labor	31
b	02-JAN-11	other	24
b	02-JAN-11	private	0
c	01-JAN-11	labor	0
c	01-JAN-11	other	0
c	01-JAN-11	private	23
c	03-JAN-11	labor	25
c	03-JAN-11	other	0
c	03-JAN-11	private	50
d	01-JAN-11	labor	0
d	01-JAN-11	other	20
d	01-JAN-11	private	0

db<>fiddle here

---

In MySQL, you can use:

SELECT nd.name,
       nd.date,
       w.work_type,
       COALESCE(t.minutes, 0) AS minutes
FROM   (SELECT DISTINCT work_type FROM table1) w
       CROSS JOIN
       (SELECT DISTINCT name, date FROM table1) nd
       LEFT OUTER JOIN table1 t
       ON (t.name = nd.name AND t.date = nd.date AND t.work_type = w.work_type);

db<>fiddle here