CODEWITHSUNDEEP
mysqlsql
J. Ordaz
J. Ordaz

Reputation: 423

Virtual table with fixed values mysql

Is it possible to have a virtual table with fixed values?

I have the next query

SELECT 
    d1
FROM 
    (SELECT DISTINCT(date) AS d1 
     FROM table1 
     WHERE date IN ("2018-01-01","2018-05-04")
    ) t1
    RIGHT JOIN 
    (SELECT "2018-01-01","2018-05-04" ) t2
ON t1.d1=t2.?;

dates are going to be the same.

I want to get null if there is no entries with those dates.

But I need an alias in the question mark.

Or maybe I need something else.

How could I create a virtual table like this one, just to use it in a join.

-- Temp table --
value
2018-01-01
2018-05-04

I would like to avoid any CREATE (temp table).

Because with SELECT 1,2,3 I get horizontal values instead of vertical.

Maybe this example explain better. http://sqlfiddle.com/#!9/49f04/4

select date, date from dates where date in ("2018-01-02","2018-01-04","2018-01-06");

# What I want to show is
#
# date1      date2
# 2018-01-02 null
# 2018-01-04 null
# 2018-01-06 2018-01-06

As there is no info for the first two dates

Upvotes: 0

Views: 603

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270391

You can do this:

SELECT . . .
FROM (SELECT '2018-01-01' as dte UNION ALL
      SELECT '2018-05-04' as dte
     ) d LEFT JOIN
     (SELECT DISTINCT date AS d1 FROM table1 WHERE date IN ("2018-01-01","2018-05-04")) t1
    ON t1.d1 = d.dte;

This answers your specific question. I'm not sure what you really want to do, but this is a pretty common querying technique.

Upvotes: 2

Related Questions