Please start any new threads on our new site at 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 

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.EmailAddress,
T_Order_Ship.remark, T_Order_Remark.remarktxt, V_OrderCountCustomer.ordercount, V_OrderCustomerReturned.OrderReturned,
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,
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 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

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

Subscribe to my blog
Go to Top of Page

- Advertisement -