How do I return rows from one table based on an ID NOT in another table in SQL (sqlite)?-CodePudding

Hello And THANK YOU ALL!!! (SQLITE)

I have 3 Tables: Trips, Layovers, Flights. One Trip has Many Flights, and Many Layovers. All Layovers and Flights are associated with a Trip via the PID.

I need to return Trips That ARE Selected (Trips.PIsSelected=1) AND and EXCLUDE ALL Trips WHERE a FlightNumber IS Present.

    SELECT t.* FROM Trips AS t 
    LEFT JOIN Flights AS f ON t.PID = f.PID WHERE 
    (f.FlightNumber <> 1892 AND t.PIsSelected = 1) GROUP BY f.PID

The above statement just returns all Trips with t.PIsSelected = 1... I can do the opposite and the below statement works (But this is not what I want.(Below)).

SELECT t.* FROM Trips AS t 
LEFT JOIN Flights AS f ON t.PID = f.PID WHERE 
(f.FlightNumber = 1892 AND t.PIsSelected = 1) GROUP BY f.PID

The Above Statement works, and returns results where The Trip Has PIsSelected=1 and a FlightNumber of 1892... I need to return all the results where the FlightNumber IS NOT 1892. Thank You.

I Uploaded 2 txt files to Drive (Trips.txt, and Flights.txt) Column data separated by 2 spaces as per StackOverflow recommended.

Flights

Layovers

CodePudding user response：

It's hard to figure it out without some sample data, but if your 2nd query works, maybe use that? For example

SELECT * from Trips where PID NOT IN (
    SELECT t.PID FROM Trips AS t 
    LEFT JOIN Flights AS f ON t.PID = f.PID WHERE 
    (f.FlightNumber = 1892 AND t.PIsSelected = 1) GROUP BY f.PID
)tmp and t.PIsSelected = 1

CodePudding user response：

The Legendary RB found the answer. Thank You Rick!!!

    SELECT t.* FROM Trips AS t 
    WHERE t.PIsSelected = 1 AND NOT EXISTS 
    (SELECT 1 FROM Flights f WHERE f.PID = t.PID AND f.FlightNumber = 1892)

Also Thanks yo Emmanuel H. for your leading queries ... Greatly appreciate...