MySQL - WHERE every Value in Column grouped by ID is 0-CodePudding

Given the tables with columns:

airline:
airline_id; airlinename 

flightschedule:
flight_id; monday; airline_id

Notes: "monday" can be 1 or 0 --> 1 means the flight flies on monday, 0 means it doesnt fly monday.

Now I need to show all Airlines that doesn't fly on mondays (all values of monday per airline_id equals 0).

I the basic output would be like this:

SELECT airlinename FROM airline

RIGHT JOIN flightschedule ON airline.airline_id = flightschedule.airline_id

WHERE (...) GROUP BY airlinename;

But now I need to filter the results to show airlines that never fly on monday (all values of monday per airline_id equal 0). I can't find a solution.

Does anyone got an idea what I can try?

CodePudding user response：

Ignoring the title and looking at the question as that moves me down an XY problem...

I need to show all Airlines that doesn't fly on mondays (all values of monday per airline_id equals 0).

Seems like a good use for Not Exists:

The below SQL simply says: find any occurrence where an airline has a flight on Monday and exclude it from the result list of all airlines. Thus returning only those airlines who don't fly monday... ever...for any flightschdule...

SELECT airlinename 
FROM airline AL
WHERE Not Exits (SELECT 1 
                 FROM flightschedule FS
                 WHERE AL.airline_id = FS.airline_id
                   AND Monday=1)

Subquery: return all flights which occur on monday.

Outer query: return all airlines that don't exist in the subquery. (thus not monday)

not exists has the benefit of early escape in that once it finds an occurrence, the optimizer can stop looking and move on to the next item in the list instead of having to check for every flight schedule.

The break down:

WHERE Not Exits -- return only objects which have no relation relation based on the corelation in the subquery.
SELECT 1 -- you have to return something it doens't matter what this value is it's thrown away and never used by using a hard coded constant the system doesn't have to do any extra work to lookup that value
FROM flightschdule FS -- we alias the table in the subquery so we can create a corelation between it and the outer
WHERE AL.airline_id = FS.airline_id -- This binds the inner/outer query together
AND Monday=1) -- return only flights that fly on monday

Alternatively:

SELECT distinct airlinename 
FROM airline
LEFT JOIN flightschedule 
   ON airline.airline_id = flightschedule.airline_id
  AND monday=1
WHERE flightschedule.airline_id is null;

QUERY: Return all airlines and those that have flights on monday.
Note the filter for monday is included ON the join itself; not part of the where clause so only records with a flight schedule occurring on monday would be returned. on the join. Vs if we did it in the where clause we would potentially get non-monday flights returned. So the limit on the join is necessary.
WHERE: Exclude airlines that had a flight on monday.

CodePudding user response：

You could try using a left join on the subquery for airline that fly in modanly and check for not matching value

SELECT DISTINCT airlinename 
FROM airline
LEFT JOIN
    (SELECT distinct airline_id
     FROM airline
     JOIN flightschedule ON airline.airline_id = flightschedule.airline_id 
                         AND monday = 0) a ON a.airline_id = airline.airline_id 
WHERE a.airline_id IS NULL