[SOLVED] Find users who have visited URL A and B with SQL?

Find users who have visited URL A and B with SQL?

Let's say you have a table with user, url, datetime, in which each row is a website visit.

How to find users who have visited both an URL contaning string pattern A and visited an URL containing string pattern B?

The fact it's "containing a string pattern ...", and not a simple equality makes it impossible to use a query with something like

url in ('action1.php', 'action2.php')

like in SQL querying a customer ID who ordered both product A and B.

Solution

You can use group by and having:

select user
from t
where url like '%a%' or
      url like '%b%'
group by user
having sum(url like '%a%') > 0 and
       sum(url like '%b%') > 0;

If you don't want to repeat the comparisons, you can leave out the where clause or use:

select user
from (select t.*, (url like '%a%') as has_a, (url like '%n%') as has_b
      from t
     ) t
where has_a or has_b
group by user
having sum(has_a) > 0 and
       sum(has_b) > 0;