SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Very confusing/Complex select statement
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

mike13
Posting Yak Master

Netherlands
200 Posts

Posted - 11/14/2012 :  18:24:46  Show Profile  Click to see mike13's MSN Messenger address  Reply with Quote
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

USA
35934 Posts

Posted - 11/14/2012 :  18:48:48  Show Profile  Visit tkizer's Homepage  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000