Travel consultation

Asked

Viewed 50 times

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 ?

mysql

2 answers

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

Understanding:

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:

What is the difference between INNER JOIN and OUTER JOIN?

  • 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.

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.