x

SQLserver 2005 join with multiple tables and group by

Hi ,

I have to

 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

Is there a simple way instead of selecting all the fields from the select which are not in the aggregate? Urgent!! any help appreciated

more ▼

asked Jan 10, 2011 at 01:32 PM in Default

avatar image

hanoi
41 4 4 5

I would suggest that you use 'Please' in your questions, especially when you have already used 'Urgent'. No one is paid to offer advice here and a little politeness costs you nothing when you would like some assistance.

Jan 10, 2011 at 01:59 PM Fatherjack ♦♦
(comments are locked)
10|1200 characters needed characters left

4 answers: sort voted first

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

more ▼

answered Jan 10, 2011 at 01:54 PM

avatar image

Oleg
17.2k 3 7 28

(comments are locked)
10|1200 characters needed characters left

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
more ▼

answered Jan 10, 2011 at 01:57 PM

avatar image

malpashaa
404 1 2 5

(comments are locked)
10|1200 characters needed characters left

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.

more ▼

answered Jan 10, 2011 at 01:44 PM

avatar image

WilliamD
26.2k 18 34 48

@WilliamD It looks like the question was about how to simplify the group by because it involves too many columns. The group by is needed though because there are probably multiple matches in TableB for each record in TableA, so those need to be collapsed into a single record. I think that the cross apply is the simplest way to do it.

Jan 10, 2011 at 01:57 PM Oleg

Ok - everyone can ignore my answer. I totally misunderstood the question.

Jan 10, 2011 at 02:02 PM WilliamD

@WilliamD - I get that feeling many times, most days.... although I prefer to say that I gave the right answer, it was just the wrong question... :)

Jan 10, 2011 at 02:23 PM Kev Riley ♦♦
(comments are locked)
10|1200 characters needed characters left

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

more ▼

answered Jan 14, 2011 at 01:17 PM

avatar image

hanoi
41 4 4 5

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x2017
x1069

asked: Jan 10, 2011 at 01:32 PM

Seen: 1888 times

Last Updated: Jan 10, 2011 at 01:43 PM

Copyright 2016 Redgate Software. Privacy Policy