I need some help from you smart SQL people
I have an email campaign where some people only clicked on the tracked link ABC in the email . Other people clicked on both tracked links, ABC and XYZ.
Is it possible to produce a Report with the right set of ‘Conditions’ so that the results only show those people who ONLY clicked the ABC link?
I want to restrict the report results so that anyone who clicked BOTH the ABC and XYZ links doesn’t show in the report.
Is this beyond the capabilities of Suite’s Report module or am I not smart enough to create the correct conditions?
Many thanks in advance!
Welcome back @stephenmchugh
Find an work example using sql query once Reports can not reach such request:
SELECT COUNT() num FROM campaign_trkrs cta WHERE cta.id = ct.id AND ct.id = ‘401f8c24-04b9-11ea-ba1f-022c40d974e6’
SELECT COUNT() num FROM campaign_trkrs cta WHERE cta.id = ct.id AND ct.id = ‘63fc1504-04b9-11ea-bfee-069e4f790264’
FROM campaign_log cl
INNER JOIN campaign_trkrs ct ON cl.related_id = ct.id
WHERE cl.activity_type = ‘link’ AND cl.deleted = 0
HAVING abc > 0 AND xyz = 0;
For complicated queries we use this plugin:
You can run any query and it allows you to present it in SuiteCRM.
Many thanks for the code. However, I’m getting an error and I’m at the limit of my SQL knowledge!:
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') num FROM campaign_trkrs cta WHERE cta.id = ct.id AND ct.id = ‘153abe38-b8b1-5b’ at line 7
If I add an asterisk [COUNT(*)] then the query returns those people who clicked the ‘153abe…’ link but the results also include those people who clicked the ‘24bf…’ link.
I only want those ID who clicked ‘153…’ and didn’t click ‘24bf…’
What’s the purpose of ‘num’ on lines 7 and 10?
Also, how does the logic work for the last line?
HAVING abc > 0 AND xyz = 0
I just read about mysql HAVING and it doesn’t make sense to me! It compares the rows of ‘abc’ and ‘xyz’ and only returns the rows where the contact.id exists in ‘abc’ but doesn’t exist in ‘xyz’? How it does that?!
My brain hurts
Think of HAVING as the WHERE that happens after the aggregated functions (COUNT).
So you use WHERE to restrict which records go into the COUNT,
and then you use HAVING to restrict which lines with aggregated values get displayed in the end result.
OK, so the Count function aggregates two set of results, ‘abc’ and ‘xyz’
Then the Having function compares ‘abc’ with ‘xyz’
abc > 0 means “give me all the results from abc”
xyz = 0 means “but don’t show the result if it doesn’t appear in xyz” (double negative!)
Hmmm, I think that’s the wrong way way around for my requirements.
My logic needs to be “show me all IDs from abc which are not in xyz” but that’s different, right?
My brain hurts even more now.
This does not sound like a COUNT/HAVING query to me, it sounds like a JOIN/Testing for NULLS, like one of these (just an example):
Select orphaned records from
FROM contacts_cstm ChildTable
LEFT JOIN contacts ParentTable
ON ChildTable.id_c = ParentTable.id
WHERE ParentTable.id IS NULL
As per your initial question, we have:
Note the query’s result, this looks like exactly as you would like to see, I suppose.