data result multiplied by joins

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

SQL Server 2005 Forums

Transact-SQL (2005)

data result multiplied by joins

Author

Topic

madtown_nihs
Starting Member

7 Posts

Posted - 2010-04-23 : 12:19:40

I have the following sql statement.
because the Order_Auth table has more than 1 record with a particular orderID, it is multiplying the results.
How can I join it so it only joins the top 1 from order_auth instead of multiple times?

Thanks
LM

select ordernumber as "Order No", '' as "Orig Order No", Customer_Id as "Customer No", [Order].OrderContact as "Name",
Order_Date as "Order Dt", '' as "Invoice Dt",
sum(isnull(item.Quantity,0) * isnull(item.Item_Price,0)) as "SV Shipped Sales",
sum(isnull(item.Quantity,0) * isnull(item.Item_Cost,0)) as "SV Shipped Cost",
'' as "SPR Shipped Cost",
isNull(Coupon.CouponName,'') as "Shipped Premium Cost or Coupon Code",
round([Order].Tax,2) as "Shipped Tax charged to customer",
'' as "Shipped Tax charged to OS.com",
[Order].Shipping_State as "State",
[Order].Shipping as "Freight Charge to Customer",
'' as "Freight Charege to OS.COM",
Card_Auth_Code as "Authorization Code",
'' as Variance -- "SV Shipped Cost" - "SPR Shipped Cost"
from [order] join Item on item.Order_ID = [Order].Order_Id join price on price.Product_ID = item.Item_Code
left join Coupon on Coupon.Coupon_ID = [Order].Coupon_ID

left join Order_Auth on [Order].Order_Id = Order_Auth.Order_ID

group by OrderNumber, Customer_Id, OrderContact, Order_Date, CouponName,
tax, Shipping_State, shipping, Card_Auth_Code

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-04-23 : 12:26:23

Ouch - my eyes

No, you're never too old to Yak'n'Roll if you're too young to die.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-04-23 : 12:26:44

[code]SELECT ordernumber AS "Order No",
'' AS "Orig Order No",
customer_id AS "Customer No",
[Order].ordercontact AS "Name",
order_date AS "Order Dt",
'' AS "Invoice Dt",
SUM(Isnull(item.quantity, 0) * Isnull(item.item_price, 0)) AS "SV Shipped Sales",
SUM(Isnull(item.quantity, 0) * Isnull(item.item_cost, 0)) AS "SV Shipped Cost",
'' AS "SPR Shipped Cost",
Isnull(coupon.couponname, '') AS "Shipped Premium Cost or Coupon Code",
Round([Order].tax, 2) AS "Shipped Tax charged to customer",
'' AS "Shipped Tax charged to OS.com",
[Order].shipping_state AS "State",
[Order].shipping AS "Freight Charge to Customer",
'' AS "Freight Charege to OS.COM",
card_auth_code AS "Authorization Code",
'' AS variance -- "SV Shipped Cost" - "SPR Shipped Cost"
FROM [order]
JOIN item
ON item.order_id = [Order].order_id
JOIN price
ON price.product_id = item.item_code
LEFT JOIN coupon
ON coupon.coupon_id = [Order].coupon_id
LEFT JOIN order_auth
ON [Order].order_id = order_auth.order_id
GROUP BY ordernumber,
customer_id,
ordercontact,
order_date,
couponname,
tax,
shipping_state,
shipping,
card_auth_code [/code]

No, you're never too old to Yak'n'Roll if you're too young to die.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-04-23 : 12:28:43

If there are multiple entries with the same OrderId then how would you decide which is to use?

No, you're never too old to Yak'n'Roll if you're too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources