A puzzle for the SQL people here

SuiteCRM Forum - English Language SuiteCRM General Discussion
#1

Hello all,

I need some help from you smart SQL people :slight_smile:

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!

#2

Welcome back @stephenmchugh

Find an work example using sql query once Reports can not reach such request:

SELECT
cl.campaign_id,
cl.target_type,
cl.target_id,
cl.related_id,
(
SELECT COUNT() num FROM campaign_trkrs cta WHERE cta.id = ct.id AND ct.id = ‘401f8c24-04b9-11ea-ba1f-022c40d974e6’
) abc,
(
SELECT COUNT() num FROM campaign_trkrs cta WHERE cta.id = ct.id AND ct.id = ‘63fc1504-04b9-11ea-bfee-069e4f790264’
) xyz
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;

Regards

#3

For complicated queries we use this plugin:
https://mjvn.com/index.php/2018/suitecrm-advanced-reports/

You can run any query and it allows you to present it in SuiteCRM.

BrozTechnologies

#4

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

image
image750×196 26.1 KB

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?

Steve

#5

Andopes,

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 :wink:

Steve

#6

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.

#7

Thanks pgr.

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”

and

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.

#8

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 contacts_cstm:

SELECT * 
FROM contacts_cstm ChildTable    
LEFT JOIN contacts ParentTable 
ON ChildTable.id_c = ParentTable.id
WHERE ParentTable.id IS NULL
#9

As per your initial question, we have:

sql
sql1182×579 87 KB

Note the query’s result, this looks like exactly as you would like to see, I suppose.