I have a table that records steps of a move. For example:
Date - Movement - Product
2018-30-10 - produced - id1
2018-30-11 - packed - id1
2018-30-12- dispatched - id1
2018-30-10 - produced - id2
2018-30-10 - produced - id3
2018-30-11 - packed - id3
2018-30-10 - produced - id4
I would like a search that would return the products that were packed and not shipped. But I could only do it inside a loop in my application. Can I do this with just one query?
Basically this:
SELECT l.id
FROM (SELECT id FROM movimentacao WHERE movimento = 'embalado' ) l
LEFT JOIN (SELECT id FROM movimentacao WHERE movimento = 'despachado') r USING(id)
WHERE r.id IS NULL
This virtual table (subquery) returns only those packages:
SELECT id FROM movimentacao WHERE movimento = 'embalado'
This, in turn, dispatched:
SELECT id FROM movimentacao WHERE movimento = 'despachado'
When we do
l LEFT JOIN r WHERE r.id IS NULL
We are filtering the unmatched cases on the right side, ie the condition you requested in the question.
See working in SQL Fiddle .
To better understand the types of JOIN :
You can select all packaged products and make a LEFT JOIN with the same table, only this time searching for the line with the same Produto and the despachado move. No WHERE filter to return only rows that did not find anything in JOIN :
SELECT a.Produto
FROM sua_tabela a
LEFT JOIN sua_tabela b
ON a.Produto = b.Produto AND b.Movimentacao = 'despachado'
WHERE a.Movimentacao = 'embalado' AND b.Produto IS NULL
See working in SQL Fiddle .