I am working on joining three tables together, but in one of the tables no information will be in there, unless the user opens the email we sent them
I am currently using this sql but it seems not to work correctly
SELECT email.senton, customer.*, count(tracker.viewed) as viewed
FROM email_sent AS email, customer_detail AS customer, email_tracker AS tracker
WHERE email.customer_id = customer.customer_id
AND customer.Email = tracker.emailaddress
AND customer.LeadOwnerId = '{$this->userid}'
This is due to the table email_tracker may not have the customers info in it, unless the customer has opened the email
Try this:
SELECT email.senton, customer.*, COUNT(tracker.viewed) as viewed
FROM email_sent email INNER JOIN customer_detail customer
ON email.customer_id = customer.customer_id
LEFT JOIN email_tracker tracker
ON customer.Email = tracker.emailaddress
WHERE customer.LeadOwnerId = '{$this->userid}'
The LEFT JOIN clause is used to always get columns on the left part and columns on the right part if they exist in the join...
EDITED according to your comment:
Try to change COUNT(tracker.viewed)
part with
CASE
WHEN tracker.emailaddress IS NOT NULL THEN COUNT(tracker.viewed)
ELSE 0
END
as viewed
I'm not sure it works, I cannot test it, but give it a try