Getting SQL results with inner query

I am having a problem with a SQL query:

select 
    o.orderID,c.city 
from 
    `order` o, `customer` c, `ordered_items` oi 
where 
    o.FKCustomerID = c.customerID 
    and o.orderStatus = 'IN PROGRESS' 
    and o.orderID = oi.FKOrderID 
    and (select FKDepartmentID 
         from ordered_items 
         where orderedItemsID in (select orderedItemsID 
                                  from ordered_items 
                                  where FKOrderID = o.orderID) 
           and FKDepartmentID = 11)
order by 
    c.city asc 

It gives me an error saying, nested query returns more then one row.

What I want to using that nested query is this,

In the table order id: 819-DBD-EB8-0E7 has 3 items. I want to get that order no only if all the ordered items are in department ID 11. (FKDepartmentID=11)

So there is 3 items for that order and all items are in department 11. So that order should be retrieved. If there is only 2 items in that department it should not be retrieved.

How to get that using sql query? In my query other part except inner query is ok.

Need to correct the inner query.

Thanks.

Learn to use explicit join syntax. Although that will not fix this problem, it will fix future problems before they arise.

The solution to your query is to use group by. Then count the number of departments that are not 11 -- and take only the orders where that count is 0.

select o.orderID, c.city 
from `order` o join
     `customer` c
     on o.FKCustomerID = c.customerID  join
     `ordered_items` oi 
     on o.orderID = oi.FKOrderID
where o.orderStatus = 'IN PROGRESS' 
group by o.order_id, c.city
having sum(FKDepartmentID <> 11) = 0
order by c.city asc ;

Note: Your syntax suggests that you are using MySQL. The more general having clause is:

having sum(case when FKDepartmentID <> 11 then 1 else 0 end) = 0