Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

MySQL UNION query with condition

I got 2 columns festivals and events, like

festival

id  |   name     | modified_date
-----------------------------------
12  | fest 1     |  2012-03-14 17:45:40
13  | fest 2     |  2012-03-14 17:45:40
15  | fest 4     |  2012-03-14 17:45:40
11  | fest 5     |  2012-03-14 17:45:40

events

id  |   name     | modified_date
-----------------------------------
14  | envt 1     |  2012-03-14 17:45:40
15  | envt 2     |  2012-03-14 17:45:40
16  | envt 4     |  2012-03-14 17:45:40
17  | envt 5     |  2012-03-14 17:45:40

There is a page in my application showing Recent Updates , and by clicking on each list it will redirect to corresponding event or festival.

For this I had the following query

select id,name,modified_date from events
UNION
select id,name,modified_date from festival
order by modified_date desc

This will return a table like

id  |   name     |  modified_date
-------------------------------------
12  | fest 1     |  2012-03-14 17:45:40
13  | fest 2     |  2012-03-14 17:45:40
--------------------------------------------

But my question is I need one more column in out put for understanding that row represents a event or festival .. Something like ths

id  |   name     |  modified_date         |  type
---------------------------------------------------
12  | fest 1     |  2012-03-14 17:45:40   |  festival
15  | evnt 2     |  2012-03-14 17:45:40   |  event 

Please help Thanks in advance

like image 715
ramesh Avatar asked Dec 12 '22 04:12

ramesh


2 Answers

select id, name, modified_date, 'event' as type
from events 

UNION 

select id, name, modified_date, 'festival' as type
from festival 
order by modified_date desc
like image 86
Michael Fredrickson Avatar answered Dec 26 '22 22:12

Michael Fredrickson


This should do the trick

select id,name,modified_date, 'event' AS type from events
UNION
select id,name,modified_date, 'festival' AS type from festival order by modified_date desc
like image 34
cichy Avatar answered Dec 26 '22 21:12

cichy