CODEWITHSUNDEEP
mysqlselectsubquery
Irvıng Ngr
Irvıng Ngr

Reputation: 438

MySQL exclude rows subquery

I'm trying to get all rows but avoid those with 'departing' entry on "status" column.

I want to know that 'trips' have 'arrived' as 'status' but exclude those that already have 'departing' as 'status'.

I already tried with GROUP BY DISTINCT (without the desired result).

Datatabse:

id  trip    type    status      date
1   1260    ocean   arriving    2019-03-04
2   1260    ocean   departing   2019-03-05
3   1261    ocean   arriving    2019-03-05
4   1262    ocean   arriving    2019-03-05
5   1263    ocean   arriving    2019-03-08
6   1263    ocean   departing   2019-03-09

Query:

SELECT * FROM `test` WHERE `status` NOT IN (select `status` FROM `test` WHERE `status` =  'departing')

Result:
                id  trip    type    status      date
                1   1260    ocean   arriving    2019-03-04
                3   1261    ocean   arriving    2019-03-05
                4   1262    ocean   arriving    2019-03-05
                5   1263    ocean   arriving    2019-03-08

Desired result:

                    id  trip    type    status      date
                    3   1261    ocean   arriving    2019-03-05
                    4   1262    ocean   arriving    2019-03-05

Here is the SQL fiddle: http://sqlfiddle.com/#!9/9ec331/3

Upvotes: 0

Views: 1274

Answers (4)

forpas
forpas

Reputation: 164089

You can do it with not exists:

select * 
from test t
where status = 'arriving'
  and not exists (select 1 from test
                  where trip = t.trip and status = 'departing')

I'm not sure if you want the subquery's condition maybe like this:

where trip = t.trip and status = 'departing' and date > t.date

Upvotes: 3

Theobald Tiger
Theobald Tiger

Reputation: 21

For this example I would group the results in a nested where clause. Something like this: 

SELECT * FROM test WHERE status = 'arriving' AND trip IN (SELECT trip FROM test GROUP BY trip HAVING count(trip) = 1);

Upvotes: 0

Bernd Buffen
Bernd Buffen

Reputation: 15057

you can try this query. it only show rows where the trip number is only in 1 row.

SELECT * FROM `test` WHERE `trip` IN (
  select trip FROM `test` group by trip
  having count(*) = 1
)

Upvotes: 0

Lisbão
Lisbão

Reputation: 11

You can use the 'date' column and use a value as a parameter

SELECT * FROM test WHERE status <> 'departing' and date = '2019-03-05'

See this example:http://sqlfiddle.com/#!9/9ec331/18

Upvotes: 1

Related Questions