Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

mike13
Posting Yak Master

Netherlands
219 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
38200 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  
 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.03 seconds. Powered By: Snitz Forums 2000