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 |
|
mike13
Posting Yak Master
219 Posts |
 Posted - 2012-11-14 : 18:24:46
|
| HI all,I got this select: (but it doesn't give all the orderid back like it would with simple select * from T_order_main where Orderstatus = 1)on the orders i'm missing everything is correctSELECT DISTINCT T_Order_Main.ORDERID, T_Order_Ship.payment, T_Order_Main.Orderdate, T_Customer.FullName, T_Customer.country, T_Customer.EmailAddress, T_Order_Ship.remark, T_Order_Remark.remarktxt, V_OrderCountCustomer.ordercount, V_OrderCustomerReturned.OrderReturned, V_OrderNotShipped.notshipped,V_Order_InReadytosend.Ready,V_Order_neworders.neworder, Chargeback = CASE WHEN V_OrderChargeback_2.IP <> '' AND V_OrderChargeback_2.IP <> '0' THEN 1 WHEN V_OrderChargeback.EmailAddress <> '' THEN 1 WHEN V_OrderChargeback_1.Fullname <> '' THEN 1 ELSE 0 END, Westernunion = CASE WHEN T_Order_Ship.payment = 'westernunion' OR T_Order_Ship.payment = 'btransfer' OR T_Order_Ship.payment = 'cash' THEN 1 ELSE 0 END, T_Order_Main.Customerid, Blacklisted = CASE WHEN s.street IS NOT NULL THEN 1 WHEN i.ip IS NOT NULL AND i.ip <> '0' THEN 1 WHEN f.fullname IS NOT NULL THEN 1 WHEN T_Customer.blacklisted = 1 THEN 1 ELSE 0 END, CouponID = case when T_Order_Main.CouponID=0 Then 0 else 1 End,T_Customer.site FROM T_Order_Main INNER JOIN T_Customer ON T_Order_Main.CustomerID = T_Customer.CustomerID INNER JOIN T_Order_Ship ON T_Order_Main.ORDERID = T_Order_Ship.OrderID LEFT OUTER JOIN V_OrderChargeback V_OrderChargeback_1 ON T_Customer.FullName = V_OrderChargeback_1.FullName LEFT OUTER JOIN V_OrderChargeback V_OrderChargeback_2 ON T_Customer.Ip = V_OrderChargeback_2.Ip LEFT OUTER JOIN T_Order_Remark ON T_Order_Main.ORDERID = T_Order_Remark.orderid LEFT OUTER JOIN V_OrderCountCustomer ON T_Order_Main.CustomerID = V_OrderCountCustomer.CustomerID LEFT OUTER JOIN V_OrderCustomerReturned ON T_Order_Main.CustomerID = V_OrderCustomerReturned.CustomerID LEFT OUTER JOIN V_OrderNotShipped ON T_Order_Main.CustomerID = V_OrderNotShipped.CustomerID LEFT OUTER JOIN V_Order_InReadytosend ON T_Order_Main.CustomerID = V_Order_InReadytosend.CustomerID LEFT OUTER JOIN V_Order_neworders ON T_Order_Main.CustomerID = V_Order_neworders.CustomerID LEFT OUTER JOIN V_OrderChargeback ON T_Customer.EmailAddress = V_OrderChargeback.EmailAddress LEFT OUTER JOIN V_Blacklist s ON T_Customer.street = s.street LEFT OUTER JOIN V_Blacklist i ON T_Customer.ip = i.ip LEFT OUTER JOIN V_Blacklist f ON T_Customer.fullname = f.fullnameWHERE (T_Order_Main.Orderstatus = 1)order by T_Order_Main.ORDERID asc |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
|
|
|
|
|
Very confusing/Complex select statement