Nested 'where in' statement in query

Question

I have this SQL query:


select sum(qty) as 'total' from `sales_flat_quote_item`
where item_id not in (
  select item_id from `sales_flat_quote_item_option`
  where item_id in (
    select item_id from `sales_flat_quote_item`
    where quote_id = 12670
  ) 
  and code = 'is_gift'
)
and quote_id = 12670 and parent_item_id is null

My task is make this query using JOIN clause. Well query itself is quite weird. sales_flat_quote_item is used in both query and subquery. This cause some efficient issues.

I'm not SQL expert, so I'm asking you. I can handle one lvl of nested select queries on my own, but I don't know what to do in this situation.

We use Mysql db engine v. 5.1

I was able to make it like this:


select A.qty, A.item_id from `sales_flat_quote_item` as A 
where A.item_id not in 
(
    select B.item_id from 
    `sales_flat_quote_item_option` as B
    inner join `sales_flat_quote_item` as C
    on C.item_id = B.item_id
    where B.code = 'is_gift' and
    C.quote_id = 834
)
and A.quote_id = 834 and A.parent_item_id is null

Is it possible to make it without using any sub-queries?

JonVD · Accepted Answer · 2010-11-22 11:42:07Z

1

To work this one out you need to work from the inside out. Figure out what the inner most query returns, then match that result against the where clause, continue until you reach the top.

select item_id 
from `sales_flat_quote_item` 
where quote_id = 12670

This gives you a list of item_id's where the quote_id is 12670.

select item_id 
from `sales_flat_quote_item_option`
where item_id in (Previous Result Set)   
and code = 'is_gift'

This gives you a list of item_id's where the item_id is contained in the previous list AND the code column = 'is_gift'

select sum(qty) as 'total' 
from `sales_flat_quote_item`     
where item_id not in (Previous Result Set) 
and quote_id = 12670 and parent_item_id is null

This returns the sum total of the qty field and names it 'total' where the item_id is not in the previous result set AND has a quote_id of 12670 and does not have a parent_item_id.

That should be enough for you to re-craft your query, but if your still having trouble post me a comment and I'll see if I can rewrite it given the above information (sans table designs).

answered Nov 22, 2010 at 11:42

JonVD

4,27826 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Ventus Over a year ago

well, the select item_id from `sales_flat_quote_item_option` where item_id in (Previous Result Set) and code = 'is_gift' returns null...

JonVD Over a year ago

Please tell me you didn't execute that verbatim? Previous Result Set was me saying "get the results of the previous query and put them here".

Ventus Over a year ago

I'm not retarded you know ;) I just paste your code to save some space. 2nd level query returns null for that quote_id = 12670 :)

JonVD Over a year ago

Thank god! You never know with this site =) well if that query returns null then really the query is just select sum(qty) as 'total' from sales_flat_quote_item WHERE quote_id = 12670 and parent_item_id is null. As the middle nest returns nothing that means it selects where the item_id is not in NULL which should be all of them depending on your table design.

Collectives™ on Stack Overflow

Nested 'where in' statement in query

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related