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:

Table1_ID (PK)

Table1_ID Description

There's another table (Table2):-

Table2_ID (PK)

Table2_LinkedID (FK)

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.

First query:-

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?

Answers


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

Need Your Help

Serialize and Deserialize Date between ASP.Net MVC and ExtJS

asp.net-mvc datetime serialization extjs

I've been searching through the web to look for a solution for this but still stuck.

numpy calculate polynom efficiently

python numpy performance polynomials

I'm trying to evaluate polynomial (3'd degree) using numpy.

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.