2
I have a table that records steps of a drive.
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 to return the products that were packaged and not shipped.
But I could only do it inside a loop in my application.
I can do this with one consultation ?
5
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 packaged ones:
SELECT id FROM movimentacao WHERE movimento = 'embalado'
This, in turn, the dispatched:
SELECT id FROM movimentacao WHERE movimento = 'despachado'
When do we do
l LEFT JOIN r WHERE r.id IS NULL
we are filtering the cases without right-hand correspondence, ie the condition you asked in the question.
See working on SQL Fiddle.
To better understand the types of JOIN:
4
You can select all packaged products and make one LEFT JOIN with the same table, only this time looking for the line with the same Produto and with the movement despachado. In the WHERE Insert to return only the lines that did not find anything in the 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 on SQL Fiddle.
Browser other questions tagged mysql
You are not signed in. Login or sign up in order to post.
Note that this solution is technically similar to what Roberto had already posted before (which already received my +1, inclusive), I just did in a slightly different order to help in understanding.
– Bacco