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.
| Author |
Topic |
|
myles.musser
Starting Member
1 Post |
 Posted - 2007-08-13 : 15:43:03
|
| Hi all,I'm pretty new to anything beyond your basic select, update and insert statements and am trying to move onto more advanced queries (well, more advanced for me, anyways). I'm running a query trying to figure out refund rates for certain products. The number of purchases are in tPurchase, and the refunds are in tRefund, and I want to get the number of each products' purchases versus their refunds for a given month. Here's what I'm running:use billingselect tPurchase.productid,count(*) as transcnt, tRefund.productid,count(*) as refndcntFrom tPurchaseRIGHT JOIN tRefundON tPurchase.productid=tRefund.productidwhere tPurchase.tssubmitted between '2007-07-01' and '2007-08-01'and tRefund.tscreated between '2007-07-01' and '2007-08-01'group by tPurchase.productid,tRefund.shortcodeI can run this without errors, but I don't really get the results I'm looking for. The count data I get from the two tables is identical (which would indicate that we've got 100% refunds on certain products, which I know is not the case). Any ideas on what I'm doing wrong here? This is my first venture into using RIGHT JOIN.Thanks in advance for any help,~Myles |
|
|
ashley.sql
Constraint Violating Yak Guru
299 Posts |
Posted - 2007-08-13 : 16:03:20
|
| can you post some sample data and required result along with script to create table and few inserts in the database. see the rules of posting or help on posting before you postAshley Rhodes |
 |
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2007-08-13 : 16:29:02
|
| I just did a quick glance, but it appears that if you change RIGHT JOIN to LEFT JOIN you would get the results you want. The difference between the two is the order of the tables surrounding the JOIN. In this case the order of tPurchase and tRefund. If you want all the purchases with their associated refunds you would do: tPurchases LEFT JOIN tRefund or tRefund RIGHT JOIN tPurchases.You might want to try playing with a smaller data set so you can see the affect of using each join.Not sure if this helps, but from BOL: The Right Outer Join operator returns each row that satisfies the join of the second (bottom) input with each matching row from the first (top) input. It also returns any rows from the second input that had no matching rows in the first input, joined with NULL. If no join predicate exists in the Argument column, each row is a matching row.Where as The Left Outer Join operator returns each row that satisfies the join of the first (top) input with the second (bottom) input. It also returns any rows from the first input that had no matching rows in the second input. The nonmatching rows in the second input are returned as null values. If no join predicate exists in the Argument column, each row is a matching row.-Ryan |
|
|
|
pootle_flump
1064 Posts |
Posted - 2007-08-14 : 04:54:59
|
| http://weblogs.sqlteam.com/jeffs/archive/2006/03/14/9289.aspx |
|
|
|
|
|
|
|
|
Experimenting with RIGHT JOIN