Add missing rows to SQL query

Question

I have a data set in which I need each issue to have 3 rows. 1 for each status that is possible. Below is an example of what I currently have.

Issue  Status  Time 
-------------------
1      SLM      30
1      SNB      43
1      EOB      22
2      SLM      12
2      EOB      87

I need something like this, where is an issue doesn't have a status then a row is added and 0 is set for the time.

 Issue   Status    Time 
 ----------------------
    1      SLM      30
    1      SNB      43
    1      EOB      22
    2      SLM      12
    2      EOB      87
    2      SNB      0

How can I do this?

Do you have a table that contains the three types of Status values? — John Pasquet
– John Pasquet, Commented Jun 6, 2017 at 16:25

Vamsi Prabhala · Accepted Answer · 2017-06-06 16:26:10Z

8

Cross join id's with statuses and left join the table on to that.

select i.issue,s.status,coalesce(t.time,0) as time
from (select distinct status from tbl) s --replace this with status table if you have one
cross join (select distinct issue from tbl) i
left join tbl t on t.issue=i.issue and t.status=s.status

answered Jun 6, 2017 at 16:26

Vamsi Prabhala

49.4k4 gold badges41 silver badges64 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

autonopy Over a year ago

Fantastic answer. I was able to take this and easily expand it to my needs, simply by adding more rows of cross join for additional unique columns I have and then more t.col=i.col for the final join. So clearly written and concise, bravo!

Dale K · Accepted Answer · 2022-03-25 06:26:41Z

-1

You can easily add another row by using this sql command.

INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);

for instance if your tables name is

edited Mar 25, 2022 at 6:26

Dale K

28.1k15 gold badges59 silver badges85 bronze badges

answered Jun 6, 2017 at 16:29

Sean

1007 bronze badges

Collectives™ on Stack Overflow

Add missing rows to SQL query

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related