SQL Appending Missing Data-CodePudding

I'm trying to append placeholder devices in a table where if the device doesn't exist, add it in with dummy data.

For example:

The Original Table is my current outcome and I'm trying to get the append the missing devices from the device list with the corresponding User IDs and a 0 placeholder for the price and units. I've tried cross joining all the users and did a Union All for all users for each device with a 0 placeholder, but it'll inflate my rows too much. I also did a left join where my original table is the cross joined with the devices and is left joined to the original table to pull in the units and prices, but the numbers came back incorrect.

CodePudding user response：

Consider below

with Devices as (
  select 'TV' device union all
  select 'Phone' union all
  select 'Computer' union all
  select 'Playstation' 
)
select user_id, device, 
  ifnull(units, 0) units, 
  ifnull(price, 0) price
from (select distinct user_id from Original_Table), Devices
left join Original_Table
using (user_id, device)

if applied to sample data in your question - output is

CodePudding user response：

First you have to make a CROSS JOIN between all users and all devices (because I do not have information about your data model, I get them from OriginalTable, but you should get them from users and devices tables). Then you have to make a LEFT JOIN with OriginalTable. I used CASE statement, so when Units or Price are null (because they do not exist in OriginalTable) I set 0 instead of null.

SELECT u.User_ID, 
       d.Device,
       CASE WHEN ot.Units IS NULL THEN 0 ELSE ot.Units END AS Units,
       CASE WHEN ot.Price IS NULL THEN 0 ELSE ot.Price END AS Price
FROM (SELECT User_ID FROM OriginalTable GROUP BY User_ID) AS u
CROSS JOIN (SELECT Device FROM OriginalTable GROUP BY Device) AS d
LEFT JOIN OriginalTable ot ON ot.User_ID = u.User_ID AND ot.Device = d.Device
ORDER BY u.User_ID, d.Device;