Joins between Tracking Tables


(Alan) #1

Hi I am trying to join between

piwik_log_link_visit_action
AND
piwik_log_action

however I am getting some odd results some records in piwik_log_link_visit_action have two matching records in piwik_log_action some with the same hash and some with different types.

Can someone help me understand the intended relationship between the tracking tables

What Join should I be using to join between piwik_log_link_visit_action AND piwik_log_action

Many thanks
Alan


(Matthieu Aubry) #2

you need to match the “type” column as well when joining the tables


(Alan) #3

Hi Matt,

Thanks for your response

This is an example of my query how do I join on type ? what are the join fields

select

  LVA.idlink_va as PK,
  LVA.idvisit,      
  URL.name as URL,
  Page_Action.name as Page_Action,
  Reffering_URL.name as Reffering_URL,
  Reffering_Page_Action.name as Reffering_Page_Action

from
piwik_log_link_visit_action LVA,
piwik_log_action URL,
piwik_log_action Page_Action,
piwik_log_action Reffering_URL,
piwik_log_action Reffering_Page_Action

where LVA.idaction_url = URL.idaction
AND LVA.idaction_name = Page_Action.idaction
AND LVA.idaction_url_ref = Reffering_URL.idaction
AND LVA.idaction_name_ref = Reffering_Page_Action.idaction