Joining 3 tables skews my results??? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Joining 3 tables skews my results???

Author

Topic

noyellatmonkeys
Starting Member

15 Posts

Posted - 2009-01-22 : 20:19:03

Hello everyone,

I'm trying to join through tables and count and sum a couple of rows, but the numbers are going a bit awry when i join all three (numbers are fine when joining just the two). I have summed up what im doing below.... if anyone could help i would appreciate it.

Merchant Table
Mid = 1 name="Co Name"

Sales Table
id = 1 Mid = 1 Date = "jan 1"
id = 2 Mid = 1 Date = "jan 2"

Click Table
id = 1 Mid = 1 Date = "jan 1" Clicks = 23
id = 2 Mid = 1 Date = "jan 2" Clicks = 10
id = 3 Mid = 1 Date = "jan 3" Clicks = 3

When I Join Just the first two tables everything looks fine

select m.name, count(s.id)as salescount from merchant m join sales s on m.mid = s.mid group by m.name

Results look correct
name = "Co Name" salescount = 2

Now when I try to join the 3rd table is when my sales count gets all screwy

select m.name, count(s.id)as salescount , sum(c.clicks)as clickcount from merchant m join sales s on m.mid = s.mid join click c on c.mid = m.mid group by m.name

my results look something like this
name = "Co Name" salescount = 24 clickcount = 34

Thanks for any help!!!

wormz666
Posting Yak Master

110 Posts

Posted - 2009-01-22 : 20:29:50

select m.name, count(s.id)as salescount , sum(c.clicks)as clickcount from merchant m inner join (sales s inner join click c on c.id = s.id) on m.mid=s.mid group by m.name

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-22 : 23:25:06

[code]select m.name, count(distinct s.id)as salescount , sum(c.clicks)as clickcount from merchant m join sales s on m.mid = s.mid join click c on c.mid = m.mid group by m.name
[/code]

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-01-22 : 23:33:15

select name , salescount, sum(clicks) as clickcount
from
(select m.name,
count(s.id)as salescount ,
c.clicks
from merchant m
inner join sales s on m.mid = s.mid
inner join click c on c.mid = m.mid
group by m.name,c.clicks) s
group by s.name,s.salescount

noyellatmonkeys
Starting Member

15 Posts

Posted - 2009-01-23 : 00:33:00

Thanks for the replys everyone... I see 3 different answers to the same problem... is their an advantage to any of them?

Thanks,

~ Mauricio

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-23 : 00:49:39

check which one provides correct result. if all gives you what you want, compare execution times

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources