SQL, Find "Paths" in SQL-Rows (Group by / Distinct)?

Question

hope someone can help me with this mysql-example:

hash, version, action
a1..., A, pageview
a2..., A, pageview
a2..., B, pageview
a2..., X, lead
a3..., A, pageview
a3..., B, pageview
a4..., A, pageview
a4..., X, lead
a5..., B, pageview
a6..., A, pageview
a6..., X, lead

How can I answer following questions with one statement?

• List all hashs that have an row with version = A (only!) and another row with action=lead
• List all hashs that have an row with version = B (only!) and another row with action=lead
• List all hash that have an action with version A, another Row with version B and a row with action=lead

I tried several statements like below with select distinct and group by having, but without success

SELECT *, count(hash) as count 
FROM it_track 
GROUP BY hash, action 
having count > 1 
   and action like 'lead';

Thank you sooo much!

Jan.

Answer 1

I think you want something like this:

select hash,
       (case when sum(action = 'lead') and
                  min(version) = max(version) and
                  min(version) = 'A'
             then 'A-only, with lead'
             when sum(action = 'lead') and
                  min(version) = max(version) and
                  min(version) = 'B'
             then 'B-only, with lead'
             when sum(action = 'lead') and
                  min(version) <> max(version) 
             then 'Both, with lead'
             else 'Unknown'
      end) as which_group
from it_track
where (action = 'lead' or
       ( action = 'pageview' and version in ('A', 'B') )
      )
group by hash

Answer 2

Another version with join as:

1> select t1.hash from it_track t1, it_track t2 
   where t2.hash = t1.hash 
   and t1.version = 'A' 
   and t2.action ='lead';

2> select t1.hash from it_track t1, it_track t2 
   where t2.hash = t1.hash 
   and t1.version = 'B' 
   and t2.action ='lead';

3> select t1.hash from it_track t1, it_track t2 , it_track t3
   where t2.hash = t1.hash 
   and t3.hash = t1.hash
   and t1.version = 'A' 
   and t2.version = 'B'
   and t3.action ='lead';

Answer 3

1> select distinct t1.hash from it_track t1 where version = 'A' and exists (select 1 from it_track t2 where t1.hash = t2.hash and t2.action = 'lead');
2> select distinct t1.hash from it_track t1 where version = 'B' and exists (select 1 from it_track t2 where t1.hash = t2.hash and t2.action = 'lead');
3> select distinct t1.hash from it_track t1 where version = 'A' and exists (select 1 from it_track t2 where t2.hash = t1.hash and version = 'B') and exists (select 1 from it_track t3 where t3.action = 'lead' and t3.hash = t1.hash);