1

This is part of a query result : enter image description here

I'm going to add a constraint to my query to remove the orders that don't have an invoice_type = final, so the first three rows should be removed and the last three rows should remain in the result.

How should I do that?

3
  • 2
    Use a self-join, join the table to itself with a condition to match the order_id but have the final type filtered for the second instance.
    – jkavalik
    Jun 26, 2016 at 16:51
  • Can you provide the table structure(s) for obtaining your result set? SHOW CREATE TABLE My_Table(s)\G - It's difficult to see straight off why you just can't add "AND invoice_type = 'final'" to your query? Also, please provide the text of that query.
    – Vérace
    Jun 26, 2016 at 19:38
  • ok,ill add more details soon, but about : why you just can't add "AND invoice_type = 'final'" to your query, i should say because if an order has one final invoice then i need all invoices of that order NOT just the invoice that is of final type , and if order doesnt have any final invoice then i dont need that order at all , the All or nothing rule!
    – alex
    Jun 26, 2016 at 20:53

1 Answer 1

2

I think this query will work for you :

select 
  order_item_id,
  order_id,
  invoice_id,
  invoice_type
from your_table
where order_item_id in 
(select order_item_id from your_table where invoice_type = 'final' group by order_item_id)
3
  • @alex you can also remove group by clause .. It will work Jun 27, 2016 at 14:00
  • thanks, it seems both where in and join will do the job, but i dont know which is faster?
    – alex
    Jun 28, 2016 at 5:47
  • @alex Use EXPLAIN to find out. It depends on your schema and server version.
    – jkavalik
    Jun 28, 2016 at 15:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.