# Finding related keys across documents (Self join in SQL)

**URL:** <https://discuss.elastic.co/t/finding-related-keys-across-documents-self-join-in-sql/65039>\
**Category:** Elasticsearch\
**Created:** [November 4, 2016, 6:07pm UTC](https://discuss.elastic.co/t/finding-related-keys-across-documents-self-join-in-sql/65039 "2016-11-04T18:07:43Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![jpotts18](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/jpotts18/32/8365_2.png) [@jpotts18](https://discuss.elastic.co/u/jpotts18)\
**Post date:** [November 4, 2016, 6:07pm UTC](https://discuss.elastic.co/t/finding-related-keys-across-documents-self-join-in-sql/65039/1 "2016-11-04T18:07:43Z")

</div>

I have a document that has an `session_id`,`event_type`, and `product_id`. Something similar to

```
{ 'session_id': 1, event_type:'impression', product_id: 5 }
{ 'session_id': 2, event_type:'click', product_id: 1 }
{ 'session_id': 3, event_type:'impression', product_id: 4}
{ 'session_id': 1, event_type:'click', product_id: 5 }

```

I basically want to be able to identify if a session has had an impression **and** click on a product in two different documents.

In SQL I would get all of my sessions that had impressions and then I would join them on the clicks to find the intersection of the two sets

```
WITH impressions AS (
    SELECT DISTINCT session_id, product_id FROM data WHERE event_type = 'impression'
)

SELECT session_id, product_id
FROM data d
INNER JOIN impressions i 
    ON d.session_id = i.session_id
WHERE d.event_type = 'click'

```

Do I need to make two separate queries? Can I use a `has_parent` query?

---

<div class="post-metadata">

**Author:** ![Mark\_Harwood](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/mark_harwood/32/10538_2.png) [@Mark\_Harwood](https://discuss.elastic.co/u/Mark_Harwood)\
**Post date:** [November 4, 2016, 6:14pm UTC](https://discuss.elastic.co/t/finding-related-keys-across-documents-self-join-in-sql/65039/2 "2016-11-04T18:14:25Z")

</div>

> [@jpotts18](#):
>
> In SQL I would get all of my sessions that had impressions and then I would join them

Joins don't come cheap in distributed systems which is often the case with large, time-based indices.

If you're planning on doing a lot of session analysis consider building an entity-centric index around sessions: [https://www.youtube.com/watch?v=yBf7oeJKH2Y](https://www.youtube.com/watch?v=yBf7oeJKH2Y)

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [July 5, 2017, 10:06pm UTC](https://discuss.elastic.co/t/finding-related-keys-across-documents-self-join-in-sql/65039/3 "2017-07-05T22:06:29Z")

</div>