SELECT + count with two tables

[Bull]

Dear all,I tried to retrieve information from two tables.Here is the structure of my first table:

Table Accountsid as integername as stringetc.

And the second one:

Table emailsid as integeraccount as integerread as boolean

So this is very easy: my first table contains the email accounts and the second one, the emails as such. What I would like to retrieve is all the accounts and, for each of them, the number of unread emails. I think I am not so far away from the solution with such query:

SELECT *,count(*) as nb FROM (SELECT * FROM accounts JOIN (SELECT account, read FROM emails WHERE read=False) as emails ON accounts.id=emails.account) as accounts GROUP BY accounts.account

Problem with this query: it only retrieves the accounts which have unread emails. So how to retrieve also the accounts which all emails are read with the number of unread messages set to 0?Thank you very much for your supportBest regards

[dooberry]

Remove "WHERE read=false".Also try this instead:

SELECT account.address, count(email.id)FROM accounts INNER JOIN emails on accounts.account = emails.account

This is much more straightforward because the subviews you have written are not needed. Only use views instead of tables in a JOIN clause to condense or transform the information that you need to look at - this usually is a technique you would use where you have a table with many fields and you need to minimise the amount of network traffic / server load by reducing the data retrieved.

[Bull]

Thank you for your answer dooberry.Meanwhile, I have found the solution. Here is the query:SELECT Accounts.*, SUM (CASE WHEN emails.read=0 THEN 1 ELSE 0 END) AS nbUnread FROM Accounts LEFT OUTER JOIN emails ON Accounts.id=emails.account GROUP BY Account.id