Select over 2 select queries


I have two tables:

id  w_date        other stuff
1   2017-05-10
1   2017-05-08
3   2017-05-17


id  h_date       other stuff
1   2017-05-01
4   2017-05-06
1   2017-05-14

Now I need a query that I get all dates where id is 1.

Something like:

Select * 
from (
    select w_date as date 
    from work where id=1
  (select h_date as date from home where id=1)

Result should be:

id  date
1   2017-05-01
1   2017-05-08
1   2017-05-10
1   2017-05-14

Any ideas?

Best Answer

This is a simple UNION query:

SELECT id, date
FROM work
WHERE id = 1 
SELECT id, date
FROM home
WHERE id = 1
ORDER BY date;

I've used the UNION ALL syntax which return all rows from both source tables. Leaving the ALL keyword out ensures rows that are duplicated in both tables are only returned a single time.

I've added an ORDER BY clause at the end of the query to return the list in date ascending order, since your sample data seems to indicate that.

See the Wikipedia entry for further details.