2

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

2

It's a case of relational division.
We have assembled an arsenal of query techniques in this related answer on SO:

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.

SQL Fiddle.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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