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)
 Very confusing/Complex select statement

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 correct

SELECT 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.fullname
WHERE (T_Order_Main.Orderstatus = 1)
order by T_Order_Main.ORDERID asc

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-11-14 : 18:48:48
Maybe you need a LEFT JOIN for T_Order_Main? It's really hard to help with the limited info you've given. Show us some sample data to help illustrate the problem.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page
   

- Advertisement -