3

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?

closed as off-topic by mustaccio, Kin Shah, Philᵀᴹ, Marco, Michael Green May 24 '17 at 11:05

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Too localized - this could be because your code has a typo, basic error, or is not relevant to most of our audience. Consider revising your question so that it appeals to a broader audience. As it stands, the question is unlikely to help other users (regarding typo questions, see this meta question for background)." – mustaccio, Kin Shah, Philᵀᴹ, Marco, Michael Green
If this question can be reworded to fit the rules in the help center, please edit the question.

16

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.

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
2
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.

Not the answer you're looking for? Browse other questions tagged or ask your own question.