CODEWITHSUNDEEP
mysqlsqldatabase
rubikskube
rubikskube

Reputation: 402

Values corresponds to NOT AT ALL in MYSQL

There are three tables in MySQL which looks like this

Table:Travel             Table:Airline           Table:Location

Code   From   To         Code   Name             Port   Country
-----  ----   ---        ----   ----             ---    -------
ET     PAR    IST        ET     Ettihad          PAR    France
ET     NYC    ANK        VA     VirginAir        MER    France
VA     BER    PAR        TA     TurkishAir       IST    Turkey
TA     SIN    MER        AF     AirFlorida       SIN    Singapore
TA     SHA    SIN        VM     VimanaAir        ANK    Turkey
AF     MER    DUB

I want to find the NAME of the Airlines which DO NOT Depart FROM France AT ALL which also includes Airline that doesnot have any flight at all. Please Note that ET and AF Departs from France. So, the result table will be

Airline
-------
VirginAir
TurkishAir
VimanaAir

I have tested in with general LEFT JOIN query and found that WHERE Location.Country <> 'France' after all the joins eliminates AF but still shows ET.

The Table and Data is here for anyone to test:

CREATE TABLE Airline (
  `Code` varchar(2) NOT NULL,
  `Name` varchar(30) NOT NULL,
  PRIMARY KEY (`Code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO Airline (Code, `Name`) VALUES
('AF', 'AirFlorida'),
('ET', 'Ettihad'),
('TA', 'TurkishAir'),
('VA', 'VirginAir'),
('VM', 'VimanaAir');

CREATE TABLE Location (
  `Port` varchar(3) NOT NULL,
  Country varchar(30) NOT NULL,
  PRIMARY KEY (`Port`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO Location (Port, Country) VALUES
('ANK', 'Turkey'),
('DUB', 'Emirates'),
('IST', 'Turkey'),
('MER', 'France'),
('NYC', 'USA'),
('PAR', 'France'),
('SIN', 'Singapore');

CREATE TABLE Travel (
  `Code` varchar(2) NOT NULL,
  `From` varchar(3) NOT NULL,
  `To` varchar(3) NOT NULL,
  PRIMARY KEY (`Code`,`From`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO Travel (Code, `From`, `To`) VALUES
('AF', 'MER', 'DUB'),
('ET', 'NYC', 'ANK'),
('ET', 'PAR', 'IST'),
('TA', 'SHA', 'SIN'),
('TA', 'SIN', 'MER'),
('VA', 'BER', 'PAR');

Upvotes: 0

Views: 49

Answers (4)

blathia
blathia

Reputation: 25

Try this query. It doesnt have subquery or the "NOT IN" operation which is expensive

select al.code, al.name from airline al LEFT JOIN
(
select tr.code from travel tr inner join location lt on tr.from = lt.port
where lt.country ='France') temp on al.code =temp.code
where temp.code IS NULL

Upvotes: 0

Strawberry
Strawberry

Reputation: 33945

How about something like this...

SELECT a.*
  FROM airline a
  LEFT
  JOIN travel t
    ON t.code = a.code
  LEFT
  JOIN location l
    ON l.port = t.from
  LEFT
  JOIN airline x
    ON x.code = a.code
   AND l.country = 'France'
 WHERE l.country IS NULL;

Upvotes: 1

lupatus
lupatus

Reputation: 4248

For me it looks like you have to use subqueries - thus it will be pretty slow operation.

SELECT * FROM `Airline` `a` 
WHERE `a`.`Code` NOT IN (
    SELECT DISTINCT `t`.`Code` 
    FROM `Travel` `t` 
    JOIN `Location` `l` ON `t`.`From` = `l`.`Port` 
    WHERE `l`.`Country` = 'France')

Upvotes: 0

krokodilko
krokodilko

Reputation: 36107

Try:

select *
from Airline a
where not exists(
  select 1
  from Travel t
  join Location l
  on t.from = l.port
  where t.Code = a.code
    and l.Country = 'France'
 )

demo: http://sqlfiddle.com/#!2/0e7dd/1

Upvotes: 1

Related Questions