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  Site Related Forums  Article Discussion  Question on join in SQL query

Author  Topic  kvdev Starting Member 2 Posts Posted - 2003-12-01 : 00:08:37 I am banging my head for a small SQL query in SQLServer. I am using 3 tables to get my requred data. Table1 is the master table containing all the names of engineers. Table1 Looks like this: Engineer: -------------- 1.EngineerCode 2.EngineerName 3.EngineerType Table2 contains orderdetails- In this table I need 2 fields for having 2 conditions. Table2 looks like this: OrderDetails --------------- 1.OrderID 2.OrderDate 3.OrderStatus Table3 contains OrderSubDetails Table3 looks like this: OrderSubDetails ------------------ 1.OrderID 2.OrderSubID 3.EngineerCode Now I want No. of Orders handled by all engineers I wrote a query as below select e.EngineerCode,count(distinct (osd.OrderID)) from ((Engineer e left outer join OrderSubDetails osd on e.EngineerCode = osd.EngineerCode) left outer join OrderDetails od on od.OrderID=osd.OrderID and od.Orderdate='2003%' and od.OrderStatus=3) where e.EngineerType=1 group by e.EngineerType In this query the data displayed is same even if I change Orderdate or OrderStatus. I.e it is not considering the second join Any help is highly appreciated Thanks in advance