CODEWITHSUNDEEP
mysqlsqlselectcountunion
Piduna
Piduna

Reputation: 175

Mysql how to count data from several tables with several conditions

I have table mail:

+---------+-----------+--------------+
|    m_id |    dns_id | mail_name    | 
+---------+-----------+--------------+
|        1|         5 | myac1        | 
|        2|         5 | myac2        | 
+---------+-----------+--------------+-

dns_id it is id from domain name from table dns
m_idit is key field with id of email
mail_name name of email

Table dns

+-----------+-----------+------------+
|    dns_id |    acc_id |         dns|
+-----------+-----------+------------+
|         5 |         1 |    test.com| 
|         6 |         1 |   test2.com|            
+-----------+-----------+------------+

acc_id id of my accounts
dns name of my domains

How you can see, i have two different domains for one account. Field dns_id from table dns linked with dns_id from table mail. I have mail accounts for dns_id with id 5 and with name test.com. But i don't have mail accounts for test2.com with dns_id 6. I need to calculate count of dns_id for acc_id Simple:

select count(dns_id) from dns where acc_id = 1;

Result:

+------------------+
|   count(dns_id)  |
+------------------+
|                2 |
+------------------+
1 row in set (0.00 sec)

But, if i want to make count only for domains, that have mail account in table mail. How to do this ? I don't want to calculate domains, where i don't have mail accounts. In this case, i should have:

+------------------+
|   count(dns_id)  |
+------------------+
|                1 |
+------------------+
1 row in set (0.00 sec)

Because my dns_id with number 6 with domain test2.com does not have mail account in table mail.

Upvotes: 2

Views: 66

Answers (2)

Neha
Neha

Reputation: 197

I am sure if I understood it correctly, but can you try below query. SELECT COUNT(*) FROM dbo.DNS WHERE (acc_id=1 ) AND dns_id IN (SELECT d_id FROM dbo.Mail)

Upvotes: 0

Priyesh Kumar
Priyesh Kumar

Reputation: 2857

Try

SELECT COUNT(dns_id)
FROM dns
WHERE acc_id IN (
    SELECT m_id FROM mail
)

Upvotes: 1

Related Questions