# How to implement MYSQL union, join and left join into elasticsearch query?
**URL:** https://discuss.elastic.co/t/how-to-implement-mysql-union-join-and-left-join-into-elasticsearch-query/78669
**Category:** Elasticsearch
**Created:** [March 15, 2017, 9:33am UTC](https://discuss.elastic.co/t/how-to-implement-mysql-union-join-and-left-join-into-elasticsearch-query/78669 "2017-03-15T09:33:58Z")
**Posts on this page:** 7
**Page:** 1
### Author:  [@vinay.bhardwaj](https://discuss.elastic.co/u/vinay.bhardwaj)
#### Post date: [March 15, 2017, 9:33am UTC](https://discuss.elastic.co/t/how-to-implement-mysql-union-join-and-left-join-into-elasticsearch-query/78669/1 "2017-03-15T09:33:58Z")
How to use mysql UNION, JOIN and LEFT JOIN in elasticsearch query?
I want to implement the following MYSQL query in elasticsearch query language.
```
select
date_format(x1.week, '%Y-%m-%d') as week,
coalesce(users, 0) as users,
coalesce(pages, 0) as pages,
coalesce(downloads, 0) as downloads
from
(
select
min(date(mytime)) as week,
count(distinct uuid) as users
from
(
select
mytime,
uuid
from
stats_view_page a
join
stats_all a2
on a.payload_id = a2.id
where
code = 'CS'
and mytime > curdate() - interval (dayofweek(curdate()) + 15) day
union
select
mytime,
uuid
from
stats_download_stop a
join
stats_all a2
on a.payload_id = a2.id
where
code = 'CS'
and mytime > curdate() - interval (dayofweek(curdate()) + 15) day x
group by
if(week(mytime - interval 4 day) = 0, 52, week(mytime - interval 4 day)) limit 6
)
x1
left join
(
select
min(date(mytime)) as week,
count(*) as pages
from
stats_view_page a
join
stats_all a2
on a.payload_id = a2.id
where
code = 'CS'
and mytime > curdate() - interval (dayofweek(curdate()) + 15) day
group by
if(week(mytime - interval 4 day) = 0, 52, week(mytime - interval 4 day)) limit 6
)
x2
on x1.week = x2.week
left join
(
select
min(date(mytime)) as week,
count(*) as downloads
from
stats_download_stop a
join
stats_all a2
on a.payload_id = a2.id
where
code = 'CS'
and mytime > curdate() - interval (dayofweek(curdate()) + 15) day
group by
if(week(mytime - interval 4 day) = 0, 52, week(mytime - interval 4 day)) limit 6
)
x3
on x1.week = x3.week
order by
x1.week desc
```
Can anybody help me out ?
---
### Author:  [@Cylindric](https://discuss.elastic.co/u/Cylindric)
#### Post date: [March 15, 2017, 3:53pm UTC](https://discuss.elastic.co/t/how-to-implement-mysql-union-join-and-left-join-into-elasticsearch-query/78669/2 "2017-03-15T15:53:02Z")
Could you format that with some code tags so we have some chance to work out what it's trying to do?
---
### Author:  [@vinay.bhardwaj](https://discuss.elastic.co/u/vinay.bhardwaj)
#### Post date: [March 16, 2017, 3:31am UTC](https://discuss.elastic.co/t/how-to-implement-mysql-union-join-and-left-join-into-elasticsearch-query/78669/3 "2017-03-16T03:31:19Z")
Hi Mark,
Thanks for the reply. I edited my post. Please have a look at it
---
### Author:  [@vinay.bhardwaj](https://discuss.elastic.co/u/vinay.bhardwaj)
#### Post date: [March 20, 2017, 3:45am UTC](https://discuss.elastic.co/t/how-to-implement-mysql-union-join-and-left-join-into-elasticsearch-query/78669/4 "2017-03-20T03:45:58Z")
Is there anybody to help me out?
---
### Author:  [@Christian\_Dahlqvist](https://discuss.elastic.co/u/Christian_Dahlqvist)
#### Post date: [March 20, 2017, 12:55pm UTC](https://discuss.elastic.co/t/how-to-implement-mysql-union-join-and-left-join-into-elasticsearch-query/78669/5 "2017-03-20T12:55:33Z")
Elasticsearch is not a relational system, and does therefore not support joins. You are therefore likely to need to store your data in a denormalized form that allows you to query it the way you want. Exactly what that would like I do not know as I do not know your data and what you want to achieve with your query.
---
### Author:  [@vinay.bhardwaj](https://discuss.elastic.co/u/vinay.bhardwaj)
#### Post date: [March 21, 2017, 3:24am UTC](https://discuss.elastic.co/t/how-to-implement-mysql-union-join-and-left-join-into-elasticsearch-query/78669/6 "2017-03-21T03:24:59Z")
Thanks for the reply Christian. I would store my data in a denormalized form and then query it to achieve what I want.
---
### Author:  [@system](https://discuss.elastic.co/u/system)
#### Post date: [April 18, 2017, 3:24am UTC](https://discuss.elastic.co/t/how-to-implement-mysql-union-join-and-left-join-into-elasticsearch-query/78669/7 "2017-04-18T03:24:59Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.