CODEWITHSUNDEEP
sqlsubquery
user1787066
user1787066

Reputation: 89

SQL nested query (not exists)

I have a table called train, which has the columns number, arrive_station, depart_station

I have populated this and would like to find trains that only serve Edinburgh (i.e., all trains departing from a station only arrive to Edinburgh) I would like to use a nested query and use the not exists operator.

So far I have tried..

SELECT depart_station
FROM train
WHERE arrive_station = "edinburgh" 
  AND NOT EXISTS
  (
    SELECT arrive_station
    FROM train
    WHERE arrive_station != "edinburgh"
  );

Upvotes: 2

Views: 3368

Answers (5)

Munawar Shah
Munawar Shah

Reputation: 1

SELECT  depart_station
FROM    train t1
WHERE   arrive_station = "edinburgh" 
AND     NOT EXISTS
        (
            SELECT  arrive_station
            FROM    train t2
            WHERE   t2.arrive_station != "edinburgh"
            AND     t1.number = t2.number
        );

Very amazing logic exist in the query. It could be solved as:

select depart_station from train order by depart_station

but there is a logic that I was facing and this solved my problem too. My problem was a little bit different but anyway, this really provided a signal.

Upvotes: 0

Nalaka526
Nalaka526

Reputation: 11464

Try,

SELECT depart_station
FROM train
WHERE arrive_station = "edinburgh" 
AND depart_station NOT IN (
    SELECT depart_station
    FROM train
    WHERE arrive_station != "edinburgh" 
)

Check this SQLFiddle

Upvotes: 0

Carboxyl
Carboxyl

Reputation: 11

If I understand your question correctly, you are only interested in any train route that arrives in Edinburgh. So just do this:

SELECT * FROM train WHERE arrive_station = "Edinburgh"

Combining this with a 'NOT EXISTS' expression, which creates a table of other routes, is redundant; just drop that part.

Upvotes: 0

Eric J. Price
Eric J. Price

Reputation: 2785

You need to relate your nested query back to your outer query somehow so if number is train number you'd want to add aliases to your tables and add AND t1.number = t2.number.

SELECT  depart_station
FROM    train t1
WHERE   arrive_station = "edinburgh" 
AND     NOT EXISTS
        (
            SELECT  arrive_station
            FROM    train t2
            WHERE   t2.arrive_station != "edinburgh"
            AND     t1.number = t2.number
        );

Upvotes: 4

Colin 't Hart
Colin 't Hart

Reputation: 7729

Why do you need to use the exists operator? Surely an OR would be enough:

select *
from train
where depart_station = 'Edinburgh' or arrive_station = 'Edinburgh';

?

Upvotes: 1

Related Questions