CODEWITHSUNDEEP
sql
ESarousi
ESarousi

Reputation: 47

Finding out information from two tables - SQL

everyone! I'm at the point of my SQL learning where I'm having a hard time trying to search to explain what I'm trying to do with SQL but here's what I got.

I have two tables. Let's call the first one:

Accounts
ID - Name - Date_Created
1 -  Mark - 01-02-2003
2 -  Brett- 02-03-2004
3 -  Sarah- 03-04-2005
4 -  Felix- 04-05-2006
5 -  John - 05-06-2007

And the second one:

Documents
ID - Account ID - Date Created Source_ID
1  -     1      -   1-03-2005 -   122
2  -     1      -   3-22-2005 -   134
3  -     3      -   2-24-2004 -   113
4  -     5      -   2-22-2005 -   114
5  -     2      -   5-25-2004 -   115

What I'm trying to do is get all account names and the number of documents associated. Thanks!

Upvotes: 0

Views: 25

Answers (2)

LukeRedwood
LukeRedwood

Reputation: 1

You can either join the second table and then group the result with a count value for the ID or you can do a sub query.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269853

One simple method is a correlated subquery:

select a.*,
       (select count(*) from documents d where d.account_id = a.id)
from accounts a;

Upvotes: 1

Related Questions