I will send the Database Description in an Image.

I tried this Select but I'm afraid that this isn't right

   SELECT t.type , a.ICAOId , a.name , ci.id , c.ISOAlpha2ID , p.docReference , ti.docReference , ti.number , p.name , p.surname
FROM dbo.AirportType t 
INNER JOIN dbo.Airport a ON t.type = a.type
INNER JOIN dbo.City ci ON a.city = ci.id
INNER JOIN dbo.Country c ON ci.ISOalpha2Id = c.ISOalpha2Id
INNER JOIN dbo.Passenger p ON c.ISOalpha2Id = p.nationality
INNER JOIN dbo.Ticket ti ON p.docReference = ti.docReference
WHERE NOT ci.id = 'Tokyo'

Can you please help to get this right? enter image description here

CodePudding user response：

You could make a list of the passengers that HAVE flown to the city then use that as a subquery to select the ones not in the list

I am just going to make an example of how it should be done

Subquery:

SELECT p.id FROM passengers
JOIN tickets t ON p.id = t.passengerID
JOIN city c ON c.id = t.cityID

Now you just put that into another query that selects the elements not in it

SELECT * FROM passenger
WHERE id not in ( 
SELECT p.id FROM passengers
JOIN tickets t ON p.id = t.passengerID
JOIN city c ON c.id = t.cityID
WHERE c.name= 'tokyo'
)

Notice I didn't use your attribute names, you will have to change those.

This was a bit simplified version of what you will have to do because the city is not directly in your tickets table. So you will also have to join tickets, with coupons, and flights to get the people that have flown to a city. But from there it is the same.

Overall I believe this should help you get what you have to do.

CodePudding user response：

A minimal reproducible example is not provided.

Here is a conceptual example, that could be easily extended to a real scenario.

SQL

-- DDL and sample data population, start
DECLARE @passenger TABLE (passengerID INT PRIMARY KEY, passenger_name VARCHAR(20));
INSERT @passenger (passengerID, passenger_name) VALUES
(1, 'Anna'),
(2, 'Paul');

DECLARE @city TABLE (cityID INT PRIMARY KEY, city_name VARCHAR(20));
INSERT @city (cityID, city_name) VALUES
(1, 'Miami'),
(2, 'Orldando'),
(3, 'Tokyo');

-- Already visited cities
DECLARE @passenger_city TABLE (passengerID INT, cityID INT);
INSERT @passenger_city (passengerID, cityID) VALUES
(1, 1),
(2, 3);
-- DDL and sample data population, end

SELECT * FROM @passenger;
SELECT * FROM @city;
SELECT * FROM @passenger_city;

;WITH rs AS
(
    SELECT c.passengerID, b.cityID 
    FROM @passenger AS c
        CROSS JOIN @city AS b   -- get all possible combinations of passengers and cities
    EXCEPT  -- filter out already visited cities
    SELECT passengerID, cityID FROM @passenger_city
)
SELECT c.*, b.city_name
FROM rs
    INNER JOIN @passenger AS c ON c.passengerID = rs.passengerID
    INNER JOIN @city AS b ON b.cityID = rs.cityID
ORDER BY c.passenger_name, b.city_name;

Output