Outer Join question

Question

Outer Join question

john.clyma 97764

Mr or Mrs. 500

Points: 501
More actions
January 14, 2010 at 1:39 pm

#74581

I need to write a query that gets the sales of the last 6 months of all our members. If the member has none, I want to display a zero. All members are in the Member_Base_Data, the transactions are in the Transaciton table.
Here is my query
Select t.Buyer, ISNULL(SUM(t.amount), 0) AS Purch
From Transactions t right outer join
Member_Base_Data mbd ON t.Buyer = mbd.Trade_ID
Where (t.date > DATEADD(month, - 6, GETDATE()))
group by t.Buyer
We have 5000 members but my returned result set is only 3900.
Long term, I need to query the same time frame last year and get trends, so having all members is important.
Thanks in advance,
John

Viewing 8 posts - 1 through 7 (of 7 total)

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Please post table definitions, sample data and desired output. Read this to see the best way to post this to get quick responses.

http://www.sqlservercentral.com/articles/Best+Practices/61537/

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 2

Give this a try:

select

t.Buyer,

ISNULL(SUM(t.amount), 0) AS Purch

from

dbo.Member_Base_Data mbd

left outer join dbo.Transactions t

on (t.Buyer = mbd.Trade_ID

and (t.date > DATEADD(month, - 6, GETDATE())))

group by t.Buyer;

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

I'd select and group by the column from member base data, rather than from transactions, seeing as t.Buyer is allowed to be null due to the outer join.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

Nice catch, Gail. My mind is still on my linked server problem and didn't see that.

select

mbd.Trade_ID,

ISNULL(SUM(t.amount), 0) AS Purch

from

dbo.Member_Base_Data mbd

left outer join dbo.Transactions t

on (t.Buyer = mbd.Trade_ID

and (t.date > DATEADD(month, - 6, GETDATE())))

group by mbd.Trade_ID;

john.clyma 97764 Mr or Mrs. 500 Points: 501 More actions · Answer 5

Thanks Lynn and Gail! That worked

For clarification and education, is the "where" not needed? and is there a case where it would be needed?

Once again thanks!

John

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 6

The WHERE clause would filter the result set returned from the JOIN of the two tables. Any filtering done on the transaction table would in effect turn the left outer join into an inner join as any records where the was no matching record from the transaction table would be eliminated. This is why your original query was not returning everything from the main table. Moving the criteria on the transaction table to the join resulted in that table being filtered during the join.

If you wanted to filter the result set based on information in the main table, that would go into the WHERE clause.

gints.plivna Ten Centuries Points: 1256 More actions · Answer 7