Select over 2 select queries [closed]

Question

I have two tables:
work:

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

home:

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?

Answer 1

This is a simple UNION query:

SELECT id, date
FROM work
WHERE id = 1 
UNION ALL
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.

Answer 2

I would do something like this

select id,   
       w_date as date 
from work 
where id=1
union all
select id, 
       h_date as date 
from home 
where id=1

Answer 3

Select ID, date
from home where ID = 1
UNION all
Select ID, date
from work where ID = 1

For future posts, try to provide more info, some table and data definitions will go a long way.