CODEWITHSUNDEEP
phpmysqlsql
1mr3yn
1mr3yn

Reputation: 74

SQL : Command Create Virtual Row If Not Exist

It is possible in MySQL to create new virtual row if the data doesn't exist, depending on user condition?
My query looks like this:

 SELECT year, value, type FROM myTable 
 WHERE year BETWEEN ( '2010' AND '2013') AND type IN ('Oil', 'Gas');

Output:

   year | value | type
  ---------------------
   2013  |  0    | Oil
   2012  | 144.5 | Oil
   2012  | 434.3 | Gas
   2011  | 141.0 | Oil
   2011  | 1234  | Gas
   2010  | 4567  | Gas
  ---------------------

I want to run a query that will output records like this: 

    year | value | type
  ---------------------
   2013  |  0    | Oil
   2013  |  0    | Gas  // -> new virtual* row
   2012  | 144.5 | Oil
   2012  | 434.3 | Gas
   2011  | 141.0 | Oil
   2011  | 1234  | Gas
   2010  | 4567  | Gas
   2010  | 0     | Oil // -> new  virtual* row
  ---------------------

The user want to display both 'Gas' and 'Oil', but since year 2013 only contain type 'Oil', so I want to create new record for 'Gas' with 0 value then same goes with year '2010' (based on the desired output above).

EDIT:

   CREATE TABLE `myTable` (
   `id` int(11) NOT NULL AUTO_INCREMENT,
   `type` varchar(15) DEFAULT NULL,  
   `year` year(4) DEFAULT NULL,
   `value` varchar(50) DEFAULT NULL,
    PRIMARY KEY (`id`)
   ) ENGINE=InnoDB AUTO_INCREMENT=92278 DEFAULT CHARSET=latin1

Upvotes: 1

Views: 2141

Answers (1)

Michal Brašna
Michal Brašna

Reputation: 2323

As you mentioned you actually have 2 tables.

I would prefer not polluting database with those 0 values, but if you insist.

INSERT INTO data
SELECT
  cross_join.year as year,
  0 as value,
  cross_join.id as type_id
FROM data
RIGHT JOIN (
  SELECT types.id, year
  FROM types
  JOIN (SELECT DISTINCT year FROM data) distinct_years
) cross_join ON (cross_join.id = data.type_id AND cross_join.year = data.year)
WHERE data.year IS NULL

Another approach is to add those rows virtually during query, which I would prefer

SELECT
  COALESCE(data.year, cross_join.year) as year,
  COALESCE(data.value, 0) as value,
  COALESCE(data.type_id, cross_join.id) as type_id
FROM data
RIGHT JOIN (
  SELECT types.id, year
  FROM types
  JOIN (SELECT DISTINCT year FROM data) distinct_years
) cross_join ON (cross_join.id = data.type_id AND cross_join.year = data.year);

Demo can be found here.

Upvotes: 3

Related Questions