Getting SQL results with inner query

Question

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,

enter image description here

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.

Is the condition you are trying to fulfill the same as saying that you want that order no only if there *isn't* a linked item whose Department ID is *not* 11? — BlueMonkMN, Apr 15 '15 at 12:06
[Bad habits to kick : using old-style JOINs](https://sqlblog.org/2009/10/08/bad-habits-to-kick-using-old-style-joins) - that old-style *comma-separated list of tables* style was replaced with the *proper* ANSI `JOIN` syntax in the ANSI-**92** SQL Standard (**more than 20 years** ago) and its use is discouraged — marc_s, Apr 15 '15 at 12:25

Gordon Linoff · Accepted Answer · 2015-04-15T12:26:26.800

2

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

edited Apr 15 '15 at 12:26

answered Apr 15 '15 at 12:09

Gordon Linoff

1,242,037
58
646
786

getting an error : #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '`ordered_items` oi on o.orderID = oi.FKOrderID where o.orderStatus = 'IN P' at line 5 – chathura Apr 15 '15 at 12:13
Thanks, It works now .it should be corrected like this : group by o.orderID – chathura Apr 15 '15 at 13:08

score 0 · Answer 2 · answered Apr 15 '15 at 12:19

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

This inner query comes in your where condition which does not really make sense. That is because WHERE clause checks if a condition if true or not. You are returning a table with this select subquery and makes no sense. You probably want to do something like this: You want to check if orders corrsponding to dept 11 exist or not. You can do it like this using the EXISTS clause in sql:

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 
EXISTS (select orderedItemsID from ordered_items where FKOrderID=o.orderID and FKDepartmentID=11)
order by c.city asc

Im getting the order nos now. but need to get the order nos when all order items are in department 11. so if a single item is in another department (like 10) it should not be selected by the query. I checked your answer and when I changed a one item to another department from department 11 it also giving the order id. can you help me to correct the query? — chathura, Apr 15 '15 at 12:46

Getting SQL results with inner query

2 Answers2