I thought building this query would be pretty easy but I'm running into a lot of trouble. Here is what I have:

Table 1
This table only consists of two columns, "id" & "name". id is the primary key.

Table 2 & 3
These tables each have a number of columns. They both contain a column that matches the id column of table one. Each allows multiple entries of an id in that column.

What I Need My Query To Do
I need to return all rows from table 1. Along with that, for each unique id in table one, I need to return a count of how many entries are in table 2 with that id and a count of how many entries are in table 3 with that id. The result set would have the columns similar to:

  • id
  • name
  • count(id) AS table_2_count
  • count(id) AS table_3_count

The tricky part (for me anyway) is that I need this to happen in a single query. That query can have joins, sub queries, unions, etc. Also, I'm using SQLite (which does support what I listed). I've been trying to get this query right all night. I keep getting result sets with either one or both counts returning total table rows rows rather than the number of rows for each related id.

Any and all help will be appreciated!!!