MySQL exclude rows subquery

Question

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

For clarification, are you trying to filter out the 'arriving' rows that have the same `trip` as the 'departing' rows? It's all I can assume looking at the sample data in the SQL Fiddle; you may want to update the question with the sample data and a clear statement of what you're looking for. — cteski, Mar 22 '19 at 17:14
Your query is equivalent to: `SELECT * FROM test WHERE status <>'departing'`. Now you should explain what is so special about the rows that you want as a result. — forpas, Mar 22 '19 at 17:18
@cteski I'm sorry. I want to know that 'trips' have 'arrived' as 'status' but exclude those that already have 'departing' as 'status' — Irvıng Ngr, Mar 22 '19 at 17:29

score 3 · Accepted Answer · edited Mar 24 '19 at 17:05

3

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

edited Mar 24 '19 at 17:05

marc_s

732,580
175
1,330
1,459

answered Mar 22 '19 at 17:34

forpas

160,666
10
38
76

Thanks @Forpas, this worked! I was using a wrong statement. Thanks! – Irvıng Ngr Mar 22 '19 at 17:41

score 1 · Answer 2 · answered Mar 22 '19 at 17:24

1

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

answered Mar 22 '19 at 17:24

Lisbão

11
1

thank you but the dates are not a viable way to achieve it. – Irvıng Ngr Mar 22 '19 at 17:50

score 0 · Answer 3 · answered Mar 22 '19 at 17:26

0

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
)

answered Mar 22 '19 at 17:26

Bernd Buffen

14,525
2
24
39

Thanks, but this one does not work for me because if the number of 'trip' is registered again then it will stop showing it – Irvıng Ngr Mar 22 '19 at 17:48

score 0 · Answer 4 · answered Mar 22 '19 at 17:37

0

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);

answered Mar 22 '19 at 17:37

Theobald Tiger

21
2

Thanks, but this one does not work for me because if the number of 'trip' is registered again then it will stop showing it. – Irvıng Ngr Mar 22 '19 at 17:47

MySQL exclude rows subquery

4 Answers4

Linked