August 24, 2005 at 11:54 am
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
Please do no cross-post, we monitor all boards.
Finish the thread here :
http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=213562
September 2, 2005 at 1:36 pm
try this.
select t1.id,(select count(t1a.id)
from temp1a t1a where
t1.id=t1a.id),
(select count(t1b.id) from temp1b t1b where
t1.id=t1b.id)
from Temp1 t1
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply