SQL Select Puzzle
Ok..here's what I want to do. I've oversimplified the example below:-
I have a table (Table1) with references in like this:
There's another table (Table2):-
Table2_Status <--value is "open" or "complete"
Table2_LinkedID is linked to Table1_ID.
Ok. Now I have three queries that I want to connect together. Here is what I need.
SELECT * FROM Table1
This works fine.
I want to add two additional columns to the query. The first is the total number of records in Table2 where the foreign key equals the primary key of table1 (ie SELECT *).
The second will be a count of records where Table2_Status = 'completed'
Does this make sense?
select t1.Table1_ID, t1.Table1_Description, t2.TotalCount, t2.CompletedCount from Table1 t1 left outer join ( select Table2_LinkedID, count(*) as TotalCount, count(case when Table2_Status = 'completed' then 1 end) as CompletedCount from Table2 group by Table2_LinkedID ) t2 on t1.Table1_ID = t2.Table2_LinkedID