CODEWITHSUNDEEP
mysql
Amy Neville
Amy Neville

Reputation: 10581

MySQL Join To Get Data But Don't Exclude Rows

I have the following query and I am joining tasks_triggers_evidence.

I want to get the value of the field evidence_accepted.

But unfortunately it's excluding all the rows whenever there are no matches. I want it to return the results, but with a NULL for evidence_accepted.

So join to grab data, but not exclude any results basically. How do I do that?

SELECT o.task_id, o.task_link, SUM(t.trigger_conversions), SUM(t.trigger_reward) AS reward, e.evidence_accepted
FROM tasks AS o
INNER JOIN advertisers AS a
ON a.advertiser_id = o.task_advertiser_id
INNER JOIN tasks_triggers AS t
ON o.task_id = t.trigger_task_id
LEFT JOIN tasks_triggers_evidence AS e
ON e.evidence_trigger_id = t.trigger_id
WHERE o.task_approved = 1 AND o.task_deleted = 0 AND o.task_paused = 0 AND t.trigger_removed = 0 AND e.evidence_account_id = ?
AND a.advertiser_balance > 0
GROUP BY o.task_id
ORDER BY reward DESC

Upvotes: 0

Views: 34

Answers (1)

Nick
Nick

Reputation: 10143

Write condition e.evidence_account_id = ? not in WHERE section, but in LEFT JOIN:

SELECT o.task_id, o.task_link, SUM(t.trigger_conversions), SUM(t.trigger_reward) AS reward, e.evidence_accepted
FROM tasks AS o
INNER JOIN advertisers AS a
  ON a.advertiser_id = o.task_advertiser_id
INNER JOIN tasks_triggers AS t
  ON o.task_id = t.trigger_task_id
LEFT JOIN tasks_triggers_evidence AS e
  ON e.evidence_trigger_id = t.trigger_id AND e.evidence_account_id = ?
WHERE o.task_approved = 1 
AND o.task_deleted = 0 
AND o.task_paused = 0 
AND t.trigger_removed = 0
AND a.advertiser_balance > 0
GROUP BY o.task_id
ORDER BY reward DESC

Upvotes: 1

Related Questions