SQLserver 2005 join with multiple tables and group by

There are basically 2 ways to do it. I would like to point out though that from the query in question, there is no way to figure out the relevance of TableC and TableD, because none of the columns from any of these tables are present in the result set. In any case,

Using cross apply:

select
    a.Name, a.id, a.Type, a.date,
    b.pac, a.ConName 
    from TableA a 
    cross apply
    (
        select count(pac) pac from TableB
            where 
                Taskgenid = a.Taskgenid 
                and formgenid = a.formgenid
    ) b
    inner join TableC c on A. id = c.ID 
    inner join TableD d on D.Typeid = c.Typeid 
    where 
        a.date between @Begindate and @Enddate 
        and a.type in(1, 2, 3); 

Using join:

select
    a.Name, a.id, a.Type, a.date,
    b.pac, a.ConName 
    from TableA a inner join
    (
        select 
            Taskgenid, formgenid, 
            count(pac) pac from TableB
            group by Taskgenid, formgenid
    ) b
        on a. Taskgenid = b.Taskgenid and a.formgenid = b.formgenid
    inner join TableC c on A. id = c.ID 
    inner join TableD d on D.Typeid = c.Typeid 
    where 
        a.date between @Begindate and @Enddate 
        and a.type in(1, 2, 3);

Cross Apply looks simpler, but it will only work if the compatability level of your database is native to SQL Server 2005 (level 90).

Hope this helps.

Oleg

Try this:

WITH CTE AS
(
   SELECT a.id, COUNT(b.pac) AS cnt
     FROM TableA AS a
          INNER JOIN
          TableB AS b
          ON a.Taskgenid = b.Taskgenid
             AND a.formgenid = b.formgenid
          INNER JOIN
          TableC AS c
          ON A.id = c.ID
          INNER JOIN
          TableD AS d
          ON D.Typeid = c.Typeid
    WHERE a.date BETWEEN @Begindate AND @Enddate
      AND a.type IN(1, 2, 3)
    GROUP BY a.id
)
SELECT a.Name, a.id, a.Type, a.date, b.cnt, a.ConName
  FROM TableA AS a
       INNER JOIN
       CTE AS b
       ON b.id = a.id

I am not too sure what you mean here, if you just want the fields and not the aggregate, why not just remove the aggregate? The group by will ensure that you don't get duplicates and all is well.

You can of course, be totally OTT and pack your query inside a common table expression (CTE):

;WITH QueryWithAggregate
          AS (SELECT    a.Name,
                        a.id,
                        a.Type,
                        a.date,
                        COUNT(b.pac),
                        a.ConName
              FROM      TableA a
              JOIN      TableB b ON a.Taskgenid = b.Taskgenid
                                    AND a.formgenid = b.formgenid
              JOIN      TableC c ON A.id = c.ID
              JOIN      TableD d ON D.Typeid = c.Typeid
              WHERE     a.date BETWEEN @Begindate AND @Enddate
                        AND a.type IN (1, 2, 3)
              GROUP BY  a.Name,
                        a.id,
                        a.Type,
                        a.date,
                        a.ConName)
    SELECT  Name,
            id,
            Type,
            date,
            ConName
    FROM    QueryWithAggregate

This is of course rather silly, as the aggregate needn't be done if you don't need it.

Thanks you guys. Learned a lot and it is working!!!!