SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 joins
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

veeramaga
Starting Member

5 Posts

Posted - 10/22/2006 :  00:46:13  Show Profile  Reply with Quote
select distinct p.property_id,count(r.property_id)as total_ region,count(t.property_id) as total_tenant
from Property p
inner join
region r
on p.property_id =r.property_id
inner join
tenant t
on r.property_id = t.property_id
and p.property_id= 12
group by p.property_id

The above query is to get me the count of region and tenants for each property

in the above query I added 5 rows of property_id as 12,when I execute it separately that is, property table and region table I get 5 as the count which is correct, the same way with property table and tenant table the same 5 as count, but if I run like above, joining 3 tables I get the output of count as 25, why is it?can anyone help me out in finding out the reason?

nr
SQLTeam MVY

United Kingdom
12543 Posts

Posted - 10/22/2006 :  09:08:47  Show Profile  Visit nr's Homepage  Reply with Quote
try
count(distinct r.region_id)as total_ region,count(distinct t.tenant_id) as total_tenant

if you have those columns.
Get rid of the group by and do a select * to see why you are getting the results.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.16 seconds. Powered By: Snitz Forums 2000