Reputation: 2541
I have a table emails_accounts
with a structure like this:
╔════╦═════════════════════╦══════════════╗
║ id ║ email ║ description ║
╠════╬═════════════════════╬══════════════╣
║ 1 ║ [email protected] ║ Lorem ║
║ 2 ║ [email protected] ║ Ipsum ║
║ 3 ║ [email protected] ║ Dolor ║
║ 4 ║ [email protected] ║ Sit Amet ║
╚════╩═════════════════════╩══════════════╝
Each email is unique.
And a second table email_templates
with a structure similiar to this:
╔════╦══════════════════╦════════════╦══════════╗
║ id ║ email_title ║ email_from ║ email_to ║
╠════╬══════════════════╬════════════╬══════════╣
║ 1 ║ Test title ║ 1 ║ 3 ║
║ 2 ║ Second title ║ 2 ║ 3 ║
║ 3 ║ Some title ║ 1 ║ 1 ║
╚════╩══════════════════╩════════════╩══════════╝
email_to
and email_from
can be different, but they can be the same (as shown in the example).
What I want to achieve is to create a SQL query which gives me ALL accounts from the table emails_accounts
but with additional information - how much every account is used in a table email_templates
(it needs to check email_from
and email_to
).
I know it shouldn't be too hard, but so far I didn't manage to get the results right. My code at the moment is:
SELECT acc.* , COUNT( temp.id )
FROM emails_accounts acc
LEFT JOIN email_templates temp ON acc.id = temp.email_from
GROUP BY acc.email
But I would like to have both email_from
and email_to
counted.
I tried also this:
SELECT acc . * , COUNT( temp.id ) + COUNT( temp2.id ) AS count
FROM emails_accounts acc
LEFT JOIN email_templates temp ON acc.id = temp.email_from
LEFT JOIN email_templates temp2 ON acc.id = temp2.email_to
GROUP BY acc.email
But it gives too many results.
EDIT: I've created a fiddle with correct answer included - thanks to jaczes.
Upvotes: 1
Views: 181
Reputation: 1404
how about that ?
SELECT ea.*,efrom, eto, ifnull(efrom,0)+ifnull(eto,0) as count
from emails_accounts ea
LEFT JOIN
(select email_from,count(email_from) as efrom
FROM email_templates group by email_from)
as e_from on ea.id=email_from
LEFT JOIN
(select email_to, count(email_to) as eto
FROM email_templates group by email_to)
as e_to on ea.id=email_to
Upvotes: 1
Reputation: 6826
Use the following UNION as a virtual table in your 1st query
select email_from as email_id
,Count(*) as template_count
from email_templates
group by
email_from
union
select email_to as email_id
,Count(*) as template_count
from email_templates
group by
email_to
Upvotes: 0