I have table1 with the following data :

and have table2 only with a Date column:

I want to get output from those tables as:

Could someone help me how to join two tables to get the output like above. I tried so many ways but I couldn't solve this issue. Thank you...

CodePudding user response：

You can use CROSS JOIN after applying UNION ALL in order to return from the both tables in a row-wise manner such as

SELECT DISTINCT COALESCE(tt.ID,t1.ID) AS ID,
                COALESCE(tt.Name,t1.Name) AS Name, tt.Date
  FROM table1 AS t1
 CROSS JOIN (SELECT * FROM table1  UNION ALL
             SELECT null,null,Date FROM table2) AS tt
 ORDER BY 3

Demo