CODEWITHSUNDEEP
mysqlsql
Ju Hyun Park
Ju Hyun Park

Reputation: 37

SQL query question

I'm working with airlines, airports and their routes.
I have two tables routes and complement. The complement table was built by searching the route segments that share a node. (B is the node in the route A->B->C)

I want to build the complement table so that the destination reached through the node is unique. In other words, an airline should not be able to reach the the destination C on its own.

After building the complement table, I tried: 

SELECT t1.*
FROM complement AS t1
   , routes AS t2
WHERE t1.airline_id1 = t2.airline_id
  AND t1.source = t2.source
  AND t1.destination != t2.destination

*airline_id1 refers to the first airline that serves the route A to B. (In A->B->C route)
The original table had about 650,000 records, but I'm getting over 10 million records with this query. I am sure I'm doing something wrong with this.

Any help will be greatly appreciated!

EDIT: Some fields in complement table are airline_id1, airline_id2, source_airport, node, destination_airport

Upvotes: 2

Views: 159

Answers (1)

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115530

Does complement table have an airline_id2 field (for the B->C part of the trip)?

This will give all "complement" routes where the two parts are done by different airlines:

SELECT c.*
FROM complement AS c
WHERE c.airline_id1 != c.airline_id2

Or maybe you want:

(all "complement" routes from A to C for airlines that do not have a "direct" route from A to C).

SELECT c.*
FROM complement AS c
  LEFT JOIN routes AS r
    ON c.source_airport = r.source_airport
      AND c.destination_airport = r.destination_airport
      AND c.airline_id1 = r.airline_id
WHERE r.id IS NULL

Upvotes: 2

Related Questions