Querying on multiple joined rows I have a table called visits that is structured like so:

+----+--------------+-------------+
| id | name         | visitor_id  |
|----+--------------+-------------+
| 1  | event1       | 1           |
| 2  | event2       | 2           |
+----+--------------+-------------+


I have a table called params that is structured like so:

+----+--------------+---------------+---------------+
| id | a_key        | a_value       | visit_id      |
|----+--------------+---------------+---------------+
| 1  | key1         | value1        | 1             |
| 2  | key2         | value2        | 1             |
| 3  | key3         | value3        | 1             |
| 4  | key1         | value1        | 2             |
| 5  | key2         | value2        | 2             |
+----+--------------+---------------+---------------+


This query looks for visits with params that are 'key1' and 'value1'.

select *
from visits
right join params as p on p.visit_id = visits.id
where a_key = 'key1' and a_value = 'value1'


I need to write a query that will give me all the visits that have params 'key1', 'value1' and also 'key3', 'value3'. In this case it would only return visit 1.

What is the most performant way to achieve this?

I thought about joining the params on again, but that doesn't seem like the best solution.

Here it is in a fiddle:

http://sqlfiddle.com/#!15/06773/5
It's a case of relational-division.
We have assembled an arsenal of query techniques in this related answer on SO:


How to filter SQL results in a has-many-through relation


For your case this query will be among the fastest possible solutions:

SELECT v.*
FROM   params p1
JOIN   params p3 USING (visit_id)
JOIN   visits v  ON v.id = p3.visit_id
WHERE  p1.a_key = 'key1' AND p1.a_value = 'value1'
AND    p3.a_key = 'key3' AND p3.a_value = 'value3';


If you just need the visit_id you don't even need to join to table visits.

db&lt;>fiddle here
Old sqlfiddle.