Post reply

Help needed on 3 table query

  • August 24, 2005 at 11:57 am

    #363484

     

    using SQL Server 2000

    I am having trouble creating a query which will properly count the occurances of records from 2 'lower' tables

    I have 3 tables, table1, table1a, and table1b.  table1 is the primary (parent) table.  table1a and table1b have a foreign key value in them which is the same as the key value in table1.

    what I want to do is (in 1 query), for each record in table1, count the number of records related to it in table1a and in table1b.  my query, with left outer joins to table1a and table1b is returning incorrect count totals which happen to be a multiple of the 2 numbers I am trying to get back.

    Can what I want be done in 1 query?  if so could you give me an example.

    Thanks

    Russ...

  • August 24, 2005 at 12:01 pm

    #584725

    I think something like this will do :

    Select T.id, ISNULL(dta.TA, 0) as TA, ISNULL(dtb.TB, 0) as TB from table1 T left outer join

    (Select ida, count(*) as TA from table1A group by ida) dta

    on T.id = dta.ida left outer join

    (Select idb, count(*) as TB from table1B group by idb) dtb

    on T.id = dtb.idb

  • August 24, 2005 at 12:14 pm

    #584732

    Remi....

    Thanks for the reply.  Your SQL skills go far beyond mine (which is not hard to do).  I am going to have to study this code for awhile to understand what you are trying to do.

    Thanks again.   Russ...

  • August 24, 2005 at 12:21 pm

    #584735

    HTH... Books online is your best friend now .

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply