CODEWITHSUNDEEP
mysqlsqlselect
Matey Johnson
Matey Johnson

Reputation: 227

MySQL: Insert records virtually in SELECT statement

I get a set of result as follows

C1     C2   C3
10     2    T
10     3    E
10     6    S

I want my SELECT query in such a way that resultant records may look like

C1     C2   C3
10     2    T
10     3    E
10     4
10     5
10     6    S

where there is a blank line for the missing records. Couldn't figure out the same.

Original query: select C1, C2,C3 from Table

Upvotes: 1

Views: 69

Answers (2)

SQLRaptor
SQLRaptor

Reputation: 681

You can create a table of sequential numbers in your database, and then use an outer join to fill in the missing row values for C2. It will be very useful for other queries as well, and takes very little space. 

CREATE TABLE Numbers (Number INTEGER PRIMARY KEY);
INSERT INTO Numbers (Number) VALUES (1),(2),(3),(4),(5),(6) ...

And then:

SELECT T.C1, N.Number AS C2, T.C3 
FROM Numbers AS N LEFT OUTER JOIN T ON T.C2 = N.Number
WHERE N.Number BETWEEN (SELECT MIN(C2) FROM T) AND (SELECT MAX(C2) FROM T)
ORDER BY C2;

HTH

Upvotes: 0

D-Shih
D-Shih

Reputation: 46239

If your mysql version upper than 8.0, you can try to use cte RECURSIVE make a calendar table then do outer join

Schema (MySQL v8.0)

CREATE TABLE T(
  C1 int, 
  C2 int,
  C3 varchar(5)
);


INSERT INTO T VALUES (10,2,'T');
INSERT INTO T VALUES (10,3,'E');
INSERT INTO T VALUES (10,6,'S');

Query #1

WITH RECURSIVE CTE AS (
    SELECT C1,MIN(C2) minC2,MAX(C2) maxC2
    FROM T
    GROUP BY C1
    UNION ALL 
    SELECT C1,minC2 +1,maxC2
    FROM CTE
    WHERE minC2+1 <= maxC2
)
SELECT t1.C1,t1.minC2,t2.C3
FROM CTE t1 LEFT JOIN T t2 on t1.minC2 = t2.C2
ORDER BY C1,minC2;

| C1  | minC2 | C3  |
| --- | ----- | --- |
| 10  | 2     | T   |
| 10  | 3     | E   |
| 10  | 4     |     |
| 10  | 5     |     |
| 10  | 6     | S   |

View on DB Fiddle

Upvotes: 1

Related Questions