CODEWITHSUNDEEP
mysqlsql
Kelu Thatsall
Kelu Thatsall

Reputation: 2541

Check if row used by another table

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

Answers (2)

jaczes
jaczes

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

Declan_K
Declan_K

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

Related Questions